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.