catcon.pl (Catalog Container Script) is an Oracle utility introduced with the Multitenant Architecture (CDB/PDB) to execute SQL scripts across one or more containers (CDB root, PDBs, or all PDBs).
It is heavily used during:
- Database upgrades
- Patching (RU, RUR, OJVM)
- Running Oracle supplied scripts
- Component installation
- Post-upgrade tasks
- Custom DBA scripts across all PDBs
Why Oracle Created catcon.pl
Before Multitenant, if you had:
ORCL
you ran a script once:
@script.sql
With Multitenant:
CDB1
├── PDB1
├── PDB2
├── PDB3
└── PDB4
The script may need to run in:
Instead of connecting manually to each container, Oracle uses catcon.pl to automate the execution.
Location
Usually located under:
$ORACLE_HOME/rdbms/admin
Check:
find $ORACLE_HOME -name catcon.pl
Common path:
$ORACLE_HOME/rdbms/admin/catcon.pl
Basic Syntax
perl catcon.pl [options] script.sql
Example:
$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-b test </span>
-d /tmp </span>
test.sql
Where:
-b = base name for logs-d = script directorytest.sql = SQL script to execute
Execute Script on All PDBs
Example:
$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-b gather_stats </span>
-d /tmp </span>
gather_stats.sql
The script runs across all open containers.
Run Script Only in Specific PDB
$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-c 'PDB1' </span>
-b test </span>
-d /tmp </span>
script.sql
Here:
PDB1 only
is processed.
Run in Multiple PDBs
-c 'PDB1 PDB2 PDB3'
Example:
catcon.pl -c 'PDB1 PDB2 PDB3' script.sql
Exclude Specific PDBs
-C 'PDB$SEED'
Example:
catcon.pl -C 'PDB$SEED' script.sql
``
Common Options
| Option | Description |
|---|
| -b | Base log file name |
| -d | Script location |
| -c | Include containers |
| -C | Exclude containers |
| -n | Parallel execution |
| -l | Log directory |
| -u | Username |
| -p | Password |
Parallel Execution
Suppose you have 20 PDBs.
Without parallelism:
catcon.pl script.sql
Runs one at a time.
Use:
catcon.pl -n 8 script.sql
to execute in parallel across 8 PDBs.
Example: Gather Dictionary Statistics
Oracle commonly uses:
$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-n 4 </span>
-l /tmp/logs </span>
-b gatherstats </span>
-d $ORACLE_HOME/rdbms/admin </span>
gather_stats.sql
During Database Upgrade
After upgrading Oracle software, Oracle internally runs scripts such as:
catupgrd.sql
utlrp.sql
using catcon.
Example:
catctl.pl
internally calls:
catcon.pl
to process all PDBs.
Relationship:
catctl.pl
|
+-- catcon.pl
|
+-- Executes scripts in all containers
Example: Recompile Invalid Objects in All PDBs
Instead of:
ALTER SESSION SET CONTAINER=PDB1;
@utlrp.sql
ALTER SESSION SET CONTAINER=PDB2;
@utlrp.sql
Use:
$ORACLE_HOME/perl/bin/perl </span>
$ORACLE_HOME/rdbms/admin/catcon.pl </span>
-b utlrp </span>
-d $ORACLE_HOME/rdbms/admin </span>
utlrp.sql
``
Oracle recompiles invalid objects in all PDBs automatically.
Log Files
Suppose:
-b test
-l /tmp/logs
Oracle generates:
test0.log
test1.log
test2.log
test3.log
``
along with spool files for each container.
Very useful during:
- Upgrades
- PSU/RU patching
- Component installation
How catcon Knows the Current Container
Within the SQL script, you can identify the current container:
SELECT SYS_CONTEXT('USERENV','CON_NAME')
FROM dual;
``
When executed through catcon, each container processes the script independently.
Typical Oracle Scripts Executed with catcon
utlrp.sql
catalog.sql
catproc.sql
utluiobj.sql
dbmsupgnv.sql
gather_stats.sql
Best Practices for DBAs
Execute on all open PDBs
catcon.pl -n 8 script.sql
Keep separate log directory
-l /u01/logs
Validate PDB status before execution
SHOW PDBS;
Review logs after completion
grep -i "ORA-" *.log
Exclude PDB$SEED unless Oracle documentation requires it
-C 'PDB$SEED'
catcon.pl vs catctl.pl
| Utility | Purpose |
|---|
catcon.pl | Execute scripts across CDB/PDBs |
catctl.pl | Database upgrade orchestration tool |
dbupgrade | Wrapper around catctl.pl |
datapatch | Applies SQL patch changes using catcon internally |
In One Line
catcon.pl is Oracle's Multitenant utility that executes SQL scripts simultaneously across one or more PDBs/CDB containers, making patching, upgrades, and administrative operations manageable in environments with many PDBs.
