Running SQL against multiple pdb databases using catcon.pl

A new perl script is available (mainly designed for running oracle internal scripts i think) but can be used to run any sql against multiple PDB's

It's located in $ORACLE_HOME/rdbms/admin

and called catcon.pl

For a very simple execution we can just use it as follows:

1. create a simple .sql file containing the sql we want to run e.g

more /tmp/test.sql

SELECT SYSDATE FROM DUAL;

 2. execute this using catcon.pl

perl catcon.pl -b test_output /tmp/test.sql

this creates a series of log files (8) wtih the output of the commands - no idea why it is 8 as only 3 of the files actuallu contain output of the SQL being run.


contents of the first file shown below, not sure how useful this really is for non oracle internal scripts (catblock is the example given in the docs http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm)

I think my previous post using CDB$VIEW may possible be more useful.

 cat test_output0.log

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jul 31 17:33:38 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> Connected.
SQL>   2
Session altered.

SQL>   2
Session altered.

SQL>   2
Session altered.

SQL>   2
Session altered.

SQL> SQL>
SQL>   2
Session altered.

SQL>   2
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = CDB$ROOT ====

SQL> Connected.
SQL>   2
Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2
CATCONSECTION
--------------------------
==== CATCON EXEC ROOT ====

SQL>   2
BEGIN_RUNNING
------------------------
==== @/tmp/test.sql ====

SQL>   2
Session altered.

SQL>   2
Session altered.

SQL>
SYSDATE
---------
31-JUL-13

SQL>   2
END_RUNNING
------------------------
==== @/tmp/test.sql ====

SQL> SQL>
SQL> SQL>
SQL> Connected.
SQL>   2
Session altered.

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2
Session altered.

SQL>   2
Session altered.

SQL>   2
NOW_CONNECTED_TO
--------------------------------------------------------------------------------
==== Current Container = PDB$SEED ====

SQL>   2
CATCONSECTION
-----------------------------------
==== CATCON EXEC IN CONTAINERS ====

SQL>   2
BEGIN_RUNNING
------------------------
==== @/tmp/test.sql ====

SQL>   2
Session altered.

SQL>   2
Session altered.

SQL>
SYSDATE
---------
31-JUL-13

SQL>   2
END_RUNNING
------------------------
==== @/tmp/test.sql ====

SQL> SQL>
SQL> SQL>
SQL> ========== PROCESS ENDED SUCCESSFULLY ==========
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options