error: ORA-65096: invalid common user or role name in oracle
I just installed Oracle, and it was missing the Scott schema. So i am trying to generate it myself. I got the sql script of Scott schema, but when i try to run the query:
create user Scott identified by tiger;
it displays the following error:
ORA-65096: invalid common user or role name in oracle.
Basically it is not allowing me to create a user Scott.
Why is that, and how can I fix my problem?
Answer
99.9% of the time the error ORA-65096: invalid common user or role name means you are logged into the CDB when you should be logged into a PDB.
But if you insist on creating users the wrong way, follow the steps below.
DANGER
Setting undocumented parameters like this (as indicated by the leading underscore) should only be done under the direction of Oracle Support. Changing such parameters without such guidance may invalidate your support contract. So do this at your own risk.
Specifically, if you set "_ORACLE_SCRIPT"=true, some data dictionary changes will be made with the column ORACLE_MAINTAINED set to 'Y'. Those users and objects will be incorrectly excluded from some DBA scripts. And they may be incorrectly included in some system scripts.
If you are OK with the above risks, and don't want to create common users the correct way, use the below answer.
Before creating the user run:
alter session set "_ORACLE_SCRIPT"=true;