ORA-65096
Tried to add a user to the database, but it failed with ORA-65096.
SQL> create user hr;
create user hr
*
ERROR at line 1:
ORA-65096: invalid common user or role name
ORA-65096 mean that the database you are in is the container database (CDB) which does not allow any local user to be created. You should switch to a pluggable database (PDB) then do it or create a common user instead.
Let's check what container we are currently in.
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
It's the root container. Oracle does not allow a local user to be created in the root container.
Please note that, container database and root container are interchangable terms in all multitenant context of Oracle database.
Solutions
To make the statement work, you have several options.
Create a Local User in PDB
To create a normal and local user, you should switch to a PDB then issue it.
SQL> alter session set container=orclpdb;
Session altered.
SQL> create user hr;
User created.
Create a Common User in CDB
If you insist to create a user in the root container, you should create it as a common user. By default, a common user should begin with C##.
SQL> create user c##hr;
User created.
SQL> select created, common from dba_users where username = 'C##HR';
CREATED COM
------------------- ---
2022-05-11 21:44:40 YES
A valid common user has been created. No error ORA-65096.
Hidden Parameter _ORACLE_SCRIPT Issue
As you may have heard, setting the undocumented parameter _ORACLE_SCRIPT as TRUE enables you to create such user in CDB at session-time. However, there have some side effects been reported recently, you should consult My Oracle Support before doing it.
Thanks this is a big help of mine
I’m glad the solution is working.
Thanks, it works.
I’m glad the solution does work.
I’m glad the solution is working.
Me, too.
ORA-65096: invalid common user or role name
Yes, that’s right!
yeah! tanks my issue was resolved
My pleasure!
Thanks, it helps me a lot!!! you’re my hero haha
It’s my pleasure!
I’ve created the user as your instruction on Oracle 23C. But when i try to connect on it using the same config values as the sys user it gives ORA-01017: invalid credential or not authorized.
you need to give create session privilige as well in order to connect
I’ve solved my problem. I’ve modified the new connection properties to use service name instead of sid and gave the pdb name. Works fine now.
Good for you!