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.
The -b option is mandotary as explained in this part:
-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
- 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
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”
- There must be no space between –x and the first “
- You need to provide — to avoid confusions for the cation.pl script parameters
$ $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