catcon.pl tips

Oracle Database Tips by Donald BurlesonJuly 8, 2013

Question: What does the new 12c catcon.pl script do?

Answer: Using the new container databases in 12c, Oracle provides the catcon.pl Perl script as a method of executing SQL or SQL scripts within a container database. You can use catcon.pl to run against a single container, all containers, or exclude container with the -C argument:

In the example below, we run catcon.pl to run the catblock.sql script from the $ORACLE_HOME/rdbms/admin drectory.

$ cd $ORACLE_HOME/rdbms/admin/

$ perl catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql
Enter Password: xxxx

$ perl catcon.pl -u SYS -d $ORACLE_HOME/rdbms/admin -b catblock_output -l /tmp catblock.sql
Enter Password: xxxx

Here we see how catcon.pl places the output of the script (or SQL statement) into the /tmp directory:

$ ls -ltr /tmp/catblock_output*
-rw-r--r--. 1 oracle oinstall 5487 Jul 2 1:45 /tmp/catblock_output0.log
-rw-r--r--. 1 oracle oinstall 7320 Jul 2 1:45 /tmp/catblock_output1.log

$ cat /tmp/catblock_output1.log

In this example, we run the runme.sql script in all contains, except TOM, DICK and HARRY, using the -C argument:

export PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB

perl $ORACLE_HOME/rdbms/admin/catcon.pl -b mylog -d /home/oracle -l /home/oracle -e -c 'TOM DICK HARRY' runme.sql

Oracle shows the following arguments available to catcon.pl:

Parameter	Description
`-u`	(Optional) Specifies the username and password to connect to the root and the specified PDBs. Specify a common user with the required privileges to run the SQL script or the SQL statement. The default is "`/ AS SYSDBA`". If no password is supplied, then catcon.pl prompts for a password.
`-U`	(Optional) Specifies the username and password to connect to the root and the specified PDBs. Specify a common user with the required privileges to perform internal tasks, such as running queries on the CDB's metadata. The default is "`/ AS SYSDBA`". If no password is supplied, then catcon.pl prompts for a password.
`-d`	(Optional) Directory that contains the SQL script. The default is the current directory.
`-l`	(Optional) Directory into which catcon.pl writes log files. The default is the current directory.
`{-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.
`-p`	(Optional) Integer that specifies the degree of parallelism. This parameter specifies the current number of invocations of the catcon.pl script on the host.
`-e`	(Optional) Sets echo `ON` while running the script. The default is echo `OFF`.
`-s`	(Optional) Spools the output of every script into a file with the following name: log-file-name-base_script-name-without-extension_[container-name-if-any].default-extension
`-E`	(Optional) When set to `ON`, the default error logging table is used. `ON` is the default setting. When set to `ON`, errors are written to the table `SPERRORLOG` in the current schema in each container in which the SQL script runs. If this table does not exist in a container, then it is created automatically. When a table other than `SPERRORLOG` is specified, errors are written to the specified table. The table must exist in each container in which the SQL script runs, and the current user must have the necessary privileges to perform DML operations on the table in each of these containers. See SQL*Plus User's Guide and Reference for more information about the error logging table.
`-g`	(Optional) Turns on the generation of debugging information.
`-b`	(Mandatory) The base name for log file names

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail:

and include the URL for the page.

Burleson Consulting

The Oracle of Database Support
Oracle Performance Tuning
Remote DBA Services

��