Oracle – Multitenant – Create a sysdba Common User

😎 To create a sysdba user that can administrate all containers:

sys@ORCL> show con_id
CON_ID
------------------------------
1
sys@ORCL> show con_name

CON_NAME
------------------------------
CDB$ROOT

sys@ORCL> create user c##username identified by ;

User created.

sys@ORCL> grant create session to c##username;

Grant succeeded.

sys@ORCL> grant sysdba to c##username container = ALL;

Grant succeeded.

If it is necessary to change the common user prefix (or get rid of it!), the value of _common_user_prefix_common_user_prefix can be changed:

sys@ORCL> select rpad(ksppinm,40) ||' ----> '||ksppstvl "param, value" from x$ksppi a,x$ksppsv b
2 where a.indx=b.indx and substr(ksppinm,1,1) = '_' and ksppinm like '%common%';

param, value
_ksipc_common_sl ---->
_eliminate_common_subexpr ----> TRUE
_dump_common_subexpressions ----> FALSE
_common_data_view_enabled ----> TRUE
_common_user_prefix ----> C##
[/sql]