oracle 12c: – letting it work for me

July 4, 2013 — Leave a comment

the previous post gave a little more information about the 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;

---------- ------------------------------ -------------
	 3 DBS300_1			  NORMAL
	 4 DBS300_2			  NORMAL

both pdbs have a tablespace called users:

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

------------------------------ ------------------------------
USERS			       DBS300_2
USERS			       DBS300_1

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);

lets pass this script to and see what happens:

export PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB
perl $ORACLE_HOME/rdbms/admin/ -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

SQL> select * from u1.t1;

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


… and the second one ?

SQL> show con_name;

SQL> select * from u1.t1;

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



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 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 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.

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.