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

advertisements

_____________________________________________________________________________________________________________________

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

advertisements

 

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

_____________________________________________________________________________________________________________________

Website Stats