【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
01 02 03 | 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.
01 02 03 04 05 | 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.
01 02 03 04 05 06 07 08 09 10 11 | SQL> select name, open_mode from v$pdbs;NAME--------------------------------------------------------------------------------OPEN_MODE--------------------PDB$SEEDREAD ONLYXEPDB1READ 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.
01 02 03 | 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.
01 02 03 04 05 | SQL> show con_name;CON_NAME------------------------------XEPDB1 |
If you can confirm the database is changed to XEPDB1, try create user again.
01 02 03 | SQL> CREATE USER user01 identified by password01 default tablespace USERS;User created. |
Grant and Alter is also available.
01 02 03 04 05 06 07 | 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.
