Labels

Tuesday, March 4, 2014

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

1 comment: