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;
SQL> select sys_context('USERENV','CON_NAME') from dual;
SQL> select con_id, NAME from V$CONTAINERS
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
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;
SQL> alter user scott account lock;
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
(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
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"
'''set container="''||name||''"'' stmt from v$pdbs where name!=''PDB$SEED'''
-- 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"
-- 4) alter session
alter session &stmt;
set feed 6
col column_name clear
col stmt clear
col pdb clear
Now I run my script
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