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.
flow of the container scripts
Daniel Westermann's Blog 