Posted by Abishek Kathiresan
In 12c when you login to a CDB database you default to the CDB$ROOT container.
[oracle@rac1 db_1]$ echo $ORACLE_SIDT12
[oracle@rac1 db_1]$ ps -ef | grep pmon
oracle 21182 1 0 13:21 ? 00:00:00 ora_pmon_T12 [oracle@rac1 db_1]$ sqlplus / as sysdba
SQL> show con_name
CON_NAME
—————-
CDB$ROOT
An attempt to create users in the CDB$ROOT container may result in ORA-65096 or ORA-65049 .
SQL> create user test identified by test;
create user test identified by test
*
ERROR at line 1:
ORA-65096: invalid common user or role name
oerr ora 65096
65096, 00000, “invalid common user or role name”
// *Cause: An attempt was made to create a common user or role with a name
// that wass 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.
//
SQL> create user test identified by test container=current;
create user test identified by test container=current
*
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
oerr ora 65049
65049, 00000, “creation of local user or role is not allowed in CDB$ROOT”
// *Cause: An attempt was made to create a local user or role in CDB$ROOT.
// *Action: If trying to create a common user or role, specify CONTAINER=ALL.
//
You first need to decide if you want a “common” user in the CDB which could be used to manage multiple PDBs, or a “local” user in only one PDB. Check that you have set the container to the correct database before creating the user.
SQL> select PDB from v$services;
PDB
—————————
T12P2
T12P1
CDB$ROOT
CDB$ROOT
CDB$ROOT
CDB$ROOT
6 rows selected.
To create a “common” user in CDB$ROOT with name starting with c## :-
SQL> show con_name
CON_NAME
————
CDB$ROOT
SQL> create user c##test identified by test;
User created.
To create a “local” user in PDB T12P1 :-
SQL> alter session set container=T12P1;
Session altered.
SQL> show con_name
CON_NAME
—————
T12P1
SQL> create user test identified by test;
User created.
In PDB T12P1 we can see that DBA_USERS lists both the local user, and the common user.
SQL> select username from dba_users where username like ‘%TEST%’;
USERNAME
——————
C##TEST
TEST
