Execute SQL script all PDBs database Oracle 12c to 19c

execute Oracle sql all PDB CDB

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

Posted on