Skip to content

How to Resolve ORA-65096: invalid common user or role name

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.

4 thoughts on “How to Resolve ORA-65096: invalid common user or role name”

Leave a Reply

Your email address will not be published. Required fields are marked *