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:
- Create user with C## use as prefix.
- 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
- 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
- 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.