Run sql script or sql statements against each Pluggable database

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

 

30 december 2015|12c, Linux, Multitenancy, rdbms|

2 Comments

  1. Raj Verma 29 juli 2016 at 14:17 - Reply

    I tried to use this but my problem is that one of the many PDBs in a CDB is in mount state and the perl script just exits because of that. Do you have any option to exclude a PDB in mount state when using the perl script ?

    • Bernhard de Cock Buning 9 augustus 2016 at 06:30 - Reply

      Hello, sorry for the late response, but you can use the -c or -C and define the PDB name.
      From the manual:
      {-c|-C} (Optional) The containers in which the SQL script is run or is not run.
      The -c parameter lists the containers in which the SQL script is run.
      The -C parameter lists the containers in which the SQL script is not run.
      Specify containers in a space-delimited list of PDB names enclosed in single quotation marks.

      The -c and -C options are mutually exclusive.

Leave A Comment