oracle 12c: catcon.pl – letting it work for me

the previous post gave a little more information about the catcon.pl script. this time I will show how you may let work the script for you.

lets say you have a little script and you want to deploy this on two of your container databases. for this simple demonstration I created to PDBs ( dbs300_1 and dbs300_2 ):

set lines 264
col pdb_name for a30;
select PDB_ID,PDB_NAME,STATUS from dba_pdbs;

    PDB_ID PDB_NAME			  STATUS
---------- ------------------------------ -------------
	 2 PDB$SEED			  NORMAL
	 3 DBS300_1			  NORMAL
	 4 DBS300_2			  NORMAL

both pdbs have a tablespace called users:

select c.tablespace_name
     , v.name
  from v$containers v
     , cdb_tablespaces c
 where v.con_id = c.con_id
   and tablespace_name = 'USERS';

TABLESPACE_NAME 	       NAME
------------------------------ ------------------------------
USERS			       DBS300_2
USERS			       DBS300_1
USERS			       CDB$ROOT

i want a user, one table and a row for my two pdbs:

create user u1 identified by "u" container=current quota unlimited on users;
create table u1.t1 ( a number, b date);
insert into u1.t1 (a,b) values (1,sysdate);
commit;

lets pass this script to catcon.pl and see what happens:

export PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB
perl $ORACLE_HOME/rdbms/admin/catcon.pl -b mylog -d /home/oracle -l /home/oracle -e -c 'DBS300_1 DBS300_2' test_script.sql

how many logfile should be there now ? two:

ls -la mylog*
-rw-r----- 1 oracle oinstall 1511 27. Jun 03:58 mylog0.log
-rw-r----- 1 oracle oinstall 1511 27. Jun 03:58 mylog1.log

grep "ORA-" mylog*.log

looks fine. lets see if the user, table and row really got created:

SQL> show con_name

CON_NAME
------------------------------
DBS300_1
SQL> select * from u1.t1;

	 A B
---------- --------------------
	 1 27-JUN-2013 03:58:13

SQL> 

… and the second one ?

SQL> show con_name;

CON_NAME
------------------------------
DBS300_2
SQL> select * from u1.t1;

	 A B
---------- --------------------
	 1 27-JUN-2013 03:58:13

SQL> 

great…

but there is a side effect: if you want to drop the user in the pdb you’ll get:

SQL> drop user u1 cascade;
drop user u11 cascade
*
ERROR at line 1:
ORA-28014: cannot drop administrative users

this is a little bit strange as the test script did not create a user with “container=all”. what you need to know here is, that catcon.pl does this:

alter session set "_oracle_script"=true;

… and this somehow results in the created users to be considered administrative users although they are not. a workaround for this is:

alter session set "_oracle_script"=true;
drop user u1 cascade;

beside that catcon.pl may be a great way to deploy scripts to all or some of your pdbs, e.g. you might want to setup development pdbs which all look the same or want to do some maintenance tasks on all the pdbs.