If you follow Oracle recommendation to use SingleTenant CDB instead of Non-CDB, then a lot of things will break.
I won’t go into details, but basically, in this new architecture, you have one core container, called CDB$ROOT, which is the default container where you connect to if you connect locally
sqlplus / as sysdba SQL> select cdb from v$database; CDB --- YES SQL> select sys_context('USERENV','CON_NAME') from dual; SYS_CONTEXT('USERENV','CON_NAME') --------------------------------- CDB$ROOT SQL> select con_id, NAME from V$CONTAINERS CON_ID NAME ---------- ---------- 1 CDB$ROOT 2 PDB$SEED 3 ST01
Then you’ll soon realise, you can no longer do what you used to do
SQL> create user u identified by u; create user u identified by u * ERROR at line 1: ORA-65096: invalid common user or role name
Some scripts still run in the root container. SHUTDOWN ABORT, ALTER SYSTEM SWITCH LOGFILE. Doing a full backup or clone probably won’t hurt you much. Relatively..
But now let’s imagine I have a very simple and very old script to lock scott
vintage.sh: echo "alter user scott account lock;"| sqlplus -s / as sysdba
This won’t work. I need to lock scott in the SingleTenant container ST01.
I could do this in the container
SQL> alter session set container=ST01; Session altered. SQL> alter user scott account lock; User altered.
So fine, so good. NOW : how do I make this work without changing the script ?
Remember, non-cdb database, as they were used in Oracle 11, 10, 9, 8, 7 … are now deprecated. Remember, cdb is recommended. Now face it : it’ll break your dba scripts.
As a production dba, I don’t want to rewrite all the existing scripts. Some are ultra-old and used by people who did not write them.
One method for my script would be to change the container in a login script.
echo "alter session set container=ST01;" > /tmp/login.sql export ORACLE_PATH=/tmp vintage.sh Session altered. User altered.
(ORACLE_PATH in latest 12.1 and in 12.2, SQL_PATH in older release)
However, if my script must work with both CDB and non-CDB, I need to set the container in only this case.
In my login.sql, I first tried to implement some plsql logic, but alter session set container is not working (aka working with limitation) with execute immediate.
As well, I don’t want my script to break Oracle 11.
So I decide to do some sqlplus magic with defined variable.
set ver off feed off -- 1) check if the column v$database.cdb exists col column_name new_v cdb nopri def cdb=null select column_name from dba_tab_columns where owner='SYS' and table_name='V_$DATABASE' and column_name='CDB'; -- 2) if cdb is YES, then select a dynamic statement using V$PDB col pdb new_v pdb nopri def pdb="null stmt from dual where 1=0" select '''set container="''||name||''"'' stmt from v$pdbs where name!=''PDB$SEED''' pdb from v$database where &cdb='YES'; -- 3) get a dynamic alter session statement. I use a dummy flagger for non-cdb col stmt new_val stmt nopri def stmt="SET FLAGGER=OFF" select &pdb; -- 4) alter session alter session &stmt; set feed 6 col column_name clear col stmt clear col pdb clear undef cdb undef stmt undef pdb del
Now I run my script
11g: ./vintage.sh User altered. 12c-non-cdb: ./vintage.sh User altered. 12cR2-single-tenant: ./vintage.sh User altered.
DISCLAIMER: you shouldn’t use a global login.sql and you should know that secretly fixing old scripts may have side effects. Test, test and retest your code
DISCLAIMER 2: my frequent readers surely wonder if this statement generating a statement generating a statement is for an obfuscation contest