When you are using the tenant option it can be very useful to run sql script or sql statements agains all or some of the PDBS.  Of course you can logon to each individual pluggable database but it would be more useful if you do this in one run.

One of the perl scripts Oracle has provides is catcon.pl. This script used during patching and database creation can also be used for your own personal usage.

When you just call the script the help is displayed. For this case you need to focus on the last part of the help.


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl

The -b option is mandotary as explained in this part:

Mandatory:
-b base name (e.g. catcon_test) for log and spool file names
sqlplus-script - sqlplus script to run OR
SQL-statement - a statement to execute


NOTES:
- if --x is the first non-option string, it needs to be
preceeded with -- to avoid confusing module parsing options into
assuming that '-' is an option which that module is not expecting and
about which it will complain
- command line parameters to SQL scripts can be introduced using --p
interactive (or secret) parameters to SQL scripts can be introduced
using --P

 

So to execute a script or sql code, the command part will be something like:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b /tmp/output.log sqlscript.sql

When you want to execute a sql statement it will looks like:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b /tmp/output.log — –x”select con_id,name from v\$pdbs”

Remark:

  • There must be no space between –x and the first “
  • You need to provide — to avoid confusions for the cation.pl script parameters

 

Example output:

$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -b /tmp/output.log -- --x"select con_id,name from v\$pdbs"
catcon: ALL catcon-related output will be written to

[/tmp/output.log_catcon_21035.lst] catcon: See [/tmp/output.log*.log] files for output generated by scripts
catcon: See [/tmp/output.log_*.lst] files for spool files, if any


$ ls -ltr /tmp/output*
-rw-r--r--. 1 oracle oinstall 371 Dec 8 08:36 /tmp/output.log_catcon_21035.lst
-rw-r--r--. 1 oracle oinstall 4049 Dec 8 08:36 /tmp/output.log0.log
-rw-r--r--. 1 oracle oinstall 2431 Dec 8 08:36 /tmp/output.log1.log

When you open the logfiles you will notice that the logfile with catcon in the name will contain all the output.

Another example where we don’t want to add the root and seed in the result te execution will look like this.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -C 'cdb$root pdb$seed' -b /tmp/output.log -- --x"select con_id,name from v\$pdbs"

Have fun with  catcon.pl