Execute SQL script (catcon.pl) in all PDBs database Oracle

Execute SQL script in all PDBs database Oracle 12c

In a CDB Environment, the catcon.pl utility is used to run SQL scripts and SQL statements in all PDBS database provide by Oracle.

catcon.pl script can run from the root container and in specified PDBs and it also have option for log files that you can view to confirm that
the SQL script or SQL statement did run successfully.

Syntax for Catcon for excute or run the SQL Script on all PDBS in once


$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl
[-u username[/password]] [-U username[/password]] [-d directory]
[-l directory] [{-c|-C} container] [-p parallelism] [-e] [-s]
[-E { ON | errorlogging-table-other-than-SPERRORLOG } ] [-I] [-g] [-f]
-b log_file_name_base -- { SQL_script [arguments] | --x'SQL_statement' }

Options
-u for username and password connect with PDB’s or CDB for execute SQL queries or scripts.
-U for username and password with special privileges to perform internal tasks like modified metadata.
-d Directory containing SQL script
-l directory on which log file written.
-c list the containers in which SQL script is run.
-C list the containers in which SQL script is not run.
-p degree of parallelism.
-e means echo on when script running.
-s spool on
-E When ON, errors are written to the table SPERRORLOG in the current schema.
-I Identifier for Error logging option
-g generating debugging information.
-f ignore if PDB’s database is closed.
-b base name of log file (mandatory)

Example of running the Script info.sql with catcon.pl script.

— Execute the script on all database included CDB or PDBs.

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -d D:\script -b info_output info.sql

— Execute the script on HRPDB or SALESPDB
Note: ‘c’ small c is used for execute

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -U SYS -d D:\scripts -l 'D:\script\output' -c 'HRPDB SALESPDB' -b info_output info.sql

–Execute the script except HRPDB or SALESPDB
Note: ‘C’ capital C is used for except

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -U SYS -d D:\scripts -l 'D:\script\output' -C 'HRPDB SALESPDB' -b info_output info.sql

–Execute the SQL Query on all database

%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catcon.pl -u SYS -e -b info_output -- --x"SELECT * FROM DUAL"

Advertisements

One thought on “Execute SQL script (catcon.pl) in all PDBs database Oracle

  1. Pingback: Downgrade unplug and plug the Pluggable Oracle Database | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.