ORA-65096: invalid common user or role name and ORA-65049: creation of local user or role is not allowed in CDB$ROOT

This error usually occurs due to we are trying to create user ( common user ) under root container. In oracle 12c there is two type of users: common user and local user.

Common users belongs to CBD’s as well as current and future PDB’s. It means it can performed operation in Container or Pluggable according to Privileges assigned. For more information about common user.

Local users is purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB. For more information about local user.

// Consider following example in which i am trying to create common user in container root.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

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

SQL> create user scott identified by scott container=current;
create user scott identified by scott container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

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

FYI.. Due to below,its causing error....






















FIX:

//If you wish to create common user under CDB$ROOT than create user start with C## and c##, as follows:

Note:

• Common user will be created under root container only.
• Current container must be set to CDB$ROOT.

SQL> create user C##scott identified by scott;

User created.

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

User created.

OR

// Creating local user in PDB:

SQL> alter session set container=sales;

Session altered.

SQL> sho con_name

CON_NAME
——————————
SALES

SQL> create user test identified by test;

User created.