One of the today’s challenge, since Oracle 12c deprecated non-cdb, is to make the dba scripts CDB-aware.
If you are lucky enough to have no 11g around, you can mostly replace DBA_* by CDB_*
OLD:
SQL> select count(*) from dba_users;
COUNT(*)
----------
121
NEW: non-cdb
SQL> select con_id, count(*) from cdb_users group by con_id;
CON_ID COUNT(*)
------ ----------
0 121
NEW: single-tenant
SQL> select con_id, count(*) from cdb_users group by con_id;
CON_ID COUNT(*)
---------- ----------
1 23
3 39
As mentioned in a white paper :
The set container privilege has certain restrictions in PL/SQL
multitenant-security-concepts-12c-2402462.pdf
Sometimes the certain restrictions will puzzle you
SQL> set feed off serverout on
SQL> exec dbms_output.put_line('root')
root
SQL> alter session set container=dora1;
SQL> sho serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> exec dbms_output.put_line('dora1');
SQL> -- NO OUTPUT WTF !!!!
SQL> set serveroutput ON
SQL> exec dbms_output.put_line('dora1');
dora1
SQL>
The security model prevents you from using alter session (with execute immediate or like in the previous example) to execute plsql.
Now you know…
Hi Laurent,
I just want to mention two workarounds that you are aware of that allow to see dbms_output after switching a container:
1. use a custom script to switch a container that could call in a sequence:
alter session set container=dora1;
a custom login.sql or any script that could call set serverout on in SQL*Plus.
It probably makes sense to do in such a script something to restore the whole SQL*Plus state, so that SET SERVEROUTPUT ON will be reexecuted again:
store set sqlplus_settings.sql replace
alter session set container=dora1;
@sqlplus_settings.sql
I have seen that people called such scripts as setcon.sql or goto.sql and called them when they wanted to switch a container.
2. an AFTER SET CONTAINER trigger ( I am not fond of that approach):
CDB$ROOT> set serverout on
CDB$ROOT> exec dbms_output.put_line(‘root1’)
root1
PL/SQL procedure successfully completed.
CDB$ROOT> alter session set container=pdb;
Session altered.
PDB> exec dbms_output.put_line(‘pdb1’)
— NO OUTPUT —
PL/SQL procedure successfully completed.
PDB> sho serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
PDB> create or replace trigger set_container
after set container on database
declare
begin
dbms_output.enable();
dbms_output.put_line(‘set_container trigger fired!’);
end;
/ 2 3 4 5 6 7 8
Trigger created.
PDB> conn / as sysdba
Connected.
CDB$ROOT> sho serverout
serveroutput OFF
CDB$ROOT> set serverout on
CDB$ROOT> exec dbms_output.put_line(‘root1’)
root1
PL/SQL procedure successfully completed.
CDB$ROOT> alter session set container=pdb;
Session altered.
— NOTICE the line after the query output.
— SQL*Plus seems to call DBMS_OUTPUT.GET_LINES only after certain statements and ALTER SESSION is not one of them
— Now we are able to see any output without using SET SERVEROUTPUT ON explicitly as the output state of the package DBMS_OUTPUT is in line with what SQL*Plus “thinks” it should be
PDB> select * from dual;
D
–
X
set_container trigger fired!
PDB> exec dbms_output.put_line(‘pdb1’)
pdb1
PL/SQL procedure successfully completed.