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.

Error: While creating a user in the Oracle 18c container database

Error starting at line : 2 in command -
CREATE USER Monitor40 IDENTIFIED BY scott
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
Error report -
SQL Error: ORA-65096: invalid common user or role name
65096. 00000 - "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.

Solution:

We have two options:

  1. Create user with C## use as prefix.
  2. Alter the parameter _ORACLE_SCRIPT for create user in container database.

Example with 1 option, Create a user with prefix C##

SQL> create user user1 identified by user1;
create user user1 identified by user1
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


---Created user with C## prefix
SQL> create user C##user1 identified by user1;

User created.

Example for 2 option, Create a user using a hidden parameter with _ORACLE_SCRIPT

  1. Check the hidden parameter value of “_oracle_script”
col parameter for a15
col session value for a13
col "session value" for a13
col "instance value" for a14
SELECT x.ksppinm "Parameter",
       Y.ksppstvl "Session Value",
       Z.ksppstvl "Instance Value"
FROM   x$ksppi X,
 x$ksppcv Y,
 x$ksppsv Z
WHERE  x.indx = Y.indx
AND    x.indx = z.indx
AND    x.ksppinm LIKE '/_oracle%' escape '/'
order by x.ksppinm;


Parameter       Session Value Instance Value
--------------- ------------- --------------
_oracle_script  FALSE         FALSE

2. Create the user by setting it TRUE at session or system as you required (but for the system you need to reboot the Database services)

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> Create user user1 identified by user1;

User created.

Note: To make the effect at the system level

SQL> alter system set "_ORACLE_SCRIPT"=true scope=spfile;

System altered.

Note: RESTART THE ORACLE DATABASE.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.