【Oracle】How to fix ORA-65096 at creating user

Ovewivew

I encountered ORA-65096 when I tried to create user at Oracle 18c Express Edition. This is a example to fix ORA-65096.

Environments

OS : Windows10
Oracle : 18c Express Edition

Error

SQL> CREATE USER user01 identified by password01 default tablespace USERS;

ORA-65096: ORA-65096: invalid common user or role name

Step1 : Checking database

Oracle has two databases ,’CDB’ and ‘PDB’. It seems it’s not possible to create local user on CDB, so we have to check which database are used.

SQL> show con_name;

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

If you find CDB lie the above, It’s good to try change it to PDB. So now you can check available PDB with the following command.

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
--------------------
PDB$SEED
READ ONLY

XEPDB1
READ WRITE

In this example there ‘re two available PDB, PDB$SEED and XEPDB1.

XEPDB1 seems suitable here, so you can change to it with following commmands.

SQL> alter session set container = XEPDB1;

Session altered.

Step2 : Create user again

At first, it’s good to check if the database was changed like the following.

SQL> show con_name;

CON_NAME
------------------------------
XEPDB1

If you can confirm the database is changed to XEPDB1, try create user again.

SQL> CREATE USER user01 identified by password01 default tablespace USERS;

User created.

Grant and Alter is also available.

SQL> grant connect, resource to user01;

Grant succeed.

SQL> alter user user01 quota unlimited on USERS;

User altered.

Summary

Please check your database now used when you get ORA-65096 at creating user. If you are using CDB, try to change it to PDB and create user again.