Oracle 12c:--ORA-65096: invalid common user or role name

Command prompt

SQL> create user 12ctest identified by 12ctest;

create user 12ctest identified by 12ctest

            *

ERROR at line 1:

ORA-01935: missing user or role name

Sql developer:-

create user 12ctest identified by 12ctest

Error at Command Line : 1 Column : 13

Error report -

SQL Error: ORA-01935: missing user or role name

01935. 00000 -  "missing user or role name"

*Cause:    A user or role name was expected.

*Action:   Specify a user or role name.

SOLUTIONS:-

In Oracle 12c one can create either common user or local user. Common user is created in container db which started with C## where as local user is created in individual pdb.

SQL> sho con_name

CON_NAME

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

CDB$ROOT

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE

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

PDB$SEED                       READ ONLY

PDBORA12C                      MOUNTED

SUBUPDB                        READ ONLY

TEST                           READ WRITE

In order to create a "common" user in CDB$ROOT with name starting with c## 

SQL> create user c##12ctest identified by test;

User created.

To create a "local" user in TEST :-

SQL> alter session set container=TEST;

Session altered.

SQL> create user c##12ctest identified by test;

create user c##12ctest identified by test

            *

ERROR at line 1:

ORA-65094: invalid local user or role name

Note:-

The reason for the error is that Local user name cannot be started with C##.

SQL> create user test identified by test;

User created.

In TEST we can see that DBA_USERS lists both the local user, and the common user.

SQL> sho con_name

CON_NAME

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

TEST

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

USERNAME             COMMON

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

C##12CTEST           YES

TEST                 NO