error ORA-65096 invalid common user or role name in oracle

Question

I have just completed installing 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 1

To start with, the oerr utility is not useful for the ORA-65096 ERROR mentioned below:-


ORA-65096: invalid common user or role name

Cause: An attempt was made to create a common user or role with a name that was not valid for common users or roles. In addition to the usual rules for user and role names, common user and role names must start with C## or c## and consist only of ASCII characters.

Action: Specify a valid common user or role name.

The solution to the ORA-06596 is to set a hidden parameter "_oracle_script".  When you set the undocumented (hidden) parameter "_oracle_script"=true  you can create the fred user without a C## in front of the user ID.  However, this user will not used useful in a pluggable/container database:

connect system/manager as sysdba

alter session set "_ORACLE_SCRIPT"=true;

create user fred identified by flintstone;

grant dba to pubs;

connect fred/flintstone

Always consult Oracle support before using any hidden Oracle parameters. Oracle makes a distinction in multitenant databases for local vs. "common" user ID's wherein, the Common user IDs must always be created at the CDB level and start with a C### prefixes while, the Local users can only be created at the PDB layer, additionally also must use the CONTAINER clause set to CURRENT for the user to be created