Creating Common User in Oracle 12c

Oracle has introduced multi-tenant architecture in 12c. With this , we will have an instance ( Single Instance) , multiple instances ( RAC) will be hosting multiple pluggable databases ( PDBs) with in a  container database  ( CDB). 

Please note that the  CDB/PDB is extra licensing option . With the regular license , we can have a CDB with only one PDB. 

Accordingly , there are changes to how users are maintained in 12c.  There are two type of users. 

a) Common User  

b) Local User 

In short , common user is for all PDBs and Local user is local to a PDB. 


There are few rules / restrictions in creating a common user. 
One of them being , the username has to be prefixed with ( c##) . 
However  , this can be circumvented . But I would recommend against against this practice, as this is not ( likely ) supported by oracle . 

Here is an example . 


SQL> show con_name

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

SQL> create user c##zahir identified by ZahirPassword container=all;

User created.


As you can see, common user ( c##zahir) was created sucessfully . 

Let us try , by removing c##. 

SQL> create user zahirm identified by ZahirPassword container=all;
create user zahirm identified by ZahirPassword container=all
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


Let us set the UNDOCUMENTED parameter to true . 
Being this is undocumented parameter ; as like many Oracle professionals , 
I would not recommend with meddling  with these  parameters unless otherwise directed by Oracle support. 

Note : This parameter also needed , when you need to create a CDB from sqlplus. 

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> create user zahirm identified by ZahirPassword container=all;

User created.

Voila . The user was created . 


Comments Welcome.