Oracle CDB PDB: ORA-65096: invalid common user or role name; Create user Command failed

_____________________________________________________________________________________________________________________

Problem Description:

SQL> create user "mytest" identified by mytest;

create user "mytest" identified by mytest

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

Solution Description:

        Starting with Oracle Database 12c Release 1 (12.1.0.2):

The name of a common user must begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. By default, the prefix is C##.

        The name of a local user must not begin with characters that are a case-insensitive match to the prefix specified by the COMMON_USER_PREFIX initialization parameter. Regardless of the value of COMMON_USER_PREFIX, the name of a local user can never begin with C## or c##.

SQL> create user "mytest" identified by mytest;

create user "mytest" identified by mytest

            *

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

        Currently we are in container database. That is why we are unable to create a normal user.

SQL> select name from v$pdbs;

NAME

-------------------

PDB$SEED

MYDBPDB

2 rows selected.

        Connect to PDB.

SQL> alter session set container=mydbpdb;

Session altered.

SQL> create user "mytest" identified by mytest;

User created.

        Create user in CDB with C## which will be available in both CDB and PDB

SQL> conn / as sysdba

Connected.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

CDB$ROOT

SQL> create user c##mytest identified by mytest;   

User created.

SQL> select username from dba_users where username like '%TEST%';

USERNAME

--------

C##MYTES

1 row selected.

SQL> alter session set container=mydbpdb;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

MYDBPDB

SQL> select username from dba_users where username like '%TEST%';

USERNAME

--------

C##MYTES

_____________________________________________________________________________________________________________________