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
  1. 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.

Related posts

Khắc phục lỗi recovery khi OPEN RESETLOGS lỗi SYSTEM trong Oracle Database?

check the FRA usage and troubleshoot the issue in oracle DB

ORA-16086: Redo data cannot be written to the standby redo log