Mir Sayeed Hassan – Oracle Blog

ORA-65096: invalid common user or role name in Oracle 12c DB

The above issue is occur in Oracle 12c DB while creation of new database in Container.

Solution recommendation

If you want to create a user in Container DB (Oracle 12c), you need to use the prefix as “C##”
If you want to create a user in Pluggable DB (PDB) (Oracle 12c), you don’t require to use the “C##”.

Connect to the Container DB on Oracle 12c & Create a user

SQL> show con_name

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

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORAPDB READ WRITE NO

Try to create a user in Container DB

SQL> create user mir identified by mir;
create user mir identified by mir
*
ERROR at line 1:
ORA-65096: invalid common user or role name

To resolve this issue you can create a user with C## on Container DB or you can set the hidden parameter to skip “C##”

Create a user on Container with C##

SQL> create user c##mir identified by mirdba;
User created.

Now create user without C##

SQL> create user mir identified by mirdba;
User created.

SQL> grant connect,resource, dba to mir;
Grant succeeded.

SQL> conn mir/mirdba
Connected.

Connect to the “PDB DB” in Oracle 12c & Create a user

Note: To create a user in PDB, There is not restriction as shown below

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORAPDB READ WRITE NO

SQL> alter session set container=ORAPDB;
Session altered.

SQL> create user hassan identified by hassan;
User created.

SQL> grant connect,resource,dba to hassan;
Grant succeeded.

SQL> connect hassan/hassan@orapdb
Connected.

SQL> sho user
USER is "HASSAN"

Therefore we create a user in PDB without C## & Without setting the Hidden Parameter.

===== Hence tested & verified in our test env=====