Running SQL scripts against several Pluggable Databases with SQL*Plus is tedious, because the DBA have to login into every Pluggable Database and run the required script or SQL Statement .
In this article, I’ll show you how to run SQL Statements easily across all Pluggable Databases.
In a container database (CDB) Environment, the Oracle catcon.pl program is used to run a SQL scripts or SQL statements in all PDBS .
Here is the command to execute a script on all PDBS:
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS/****AhAhpassword-changed*** -d /export/home/oracle/scripts -l /export/home/oracle/scripts/logs -b give-write_ps give-write_ps.sql -u username/password -d directory for the script to run on all PDBS -l location of logs -b basename of log files
And here is the Unix shell script with a loop to run the script on all CDBs and PDBs on a server with an Oracle 19 Home:
#!/usr/bin/ksh export ORACLE_HOME=/opt/oracle/app/oracle/product/19 export PATH=$ORACLE_HOME/bin:$PATH rm /export/home/oracle/scripts/logs/my-script.log for base in `ps -ef | grep pmon | grep -v grep | awk -F_ '{print $3}'` do export ORACLE_SID=$base export ORAENV_ASK=NO . $ORACLE_HOME/bin/oraenv echo Database $base : >> /export/home/oracle/scripts/logs/my-script.log echo " " >> /export/home/oracle/scripts/logs/my-script.log $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS/****OhOhpassword-changed*** -d /export/home/oracle/scripts -l /export/home/oracle/scripts/logs -b my-action my-script.sql 2>> /export/home/oracle/scripts/logs/my-script.log done
Have a nice day and if you don’t already use it, you should securely browse the internet with Brave