ORA 65096 Invalid Common User or Role Name - Oracle 12C

This post explains the reason for the weird error – ORA 65096 – Invalid Common User or Role Name that you may encounter in Oracle 12C while creating a new user / schema and offers a way to solve it. You would need a working version of Oracle 12C database and needless to say, encountered this error while you tried creating a schema with some of the commands you used before, as a reference see below
CDB$ROOT@ORCL> create user hr identified by hr 
  2  / 
create user hr identified by hr 
            * 
ERROR at line 1: 
ORA-65096: invalid common user or role name 

OERR Lookup - Error Code 65096


If you do a lookup for the error code 65096 using OERR utility, you can find the explanation as shown below
[oracle@localhost ~]$ oerr ora 65096 
65096, 00000, "invalid common user or role name" 
// *Cause:  An attempt was made to create a common user or role with a name 
//          that wass not valid for common users or roles.  In addition to 
//          the usual rules for user and role names, common user and role 
//          names must start with C## or c## and consist only of ASCII 
//          characters. 
// *Action: Specify a valid common user or role name. 
// 


This is because of the multitenant architecture introduced in Oracle 12c. You need to have an understanding on multitenant container database (CDB) and Pluggable databases (PDB) to appreciate the need for this error. In the step above, an attempt was made to create an user in CDB to manage multiple PDBs, and for some reason Oracle does not like the syntax that has been provided.

Solution - For Creating a CDB User


If the intention is to create an user in CDB than prefix “C##” to the user name as shown below;
CDB$ROOT@ORCL> create user c##hr identified by hr 
  2  / 
User created. 
CDB$ROOT@ORCL> 


Solution - For Creating a PDB User


Or, if you want to create an user in one of the PDBs from a CDB user, you need to switch to the PDB first and then create an user. This is shown below;
CDB$ROOT@ORCL> show con_name 
CON_NAME 
------------------------------ 
CDB$ROOT 
CDB$ROOT@ORCL> select pdb from v$services; 
PDB 
------------------------------ 
PDB1 
CDB$ROOT 
CDB$ROOT 
CDB$ROOT 
CDB$ROOT 
CDB$ROOT@ORCL> alter session set container=PDB1 
  2  / 
Session altered. 
CDB$ROOT@ORCL> create user hr2 identified by hr2 
  2  / 
User created. 
CDB$ROOT@ORCL> show con_name 
CON_NAME 
------------------------------ 
PDB1 
CDB$ROOT@ORCL> 

Switching to a PDB is done via alter session command, where we set the container to the name of the PDB where a user account needs to be created. The command to create an user will work normally following that.

2 comments:

  1. BUT you are not able to log in using the common user into the database. I tried it, it says invalid username and password.

    ReplyDelete
  2. Hi Donna, did you create the user in CDB or PDB? Can you post some examples?

    ReplyDelete