ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c database

ORA-65096: invalid common user or role name or ORA-65049: creation in oracle 12c


65049, 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.


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.



 Solution
================================
================================
(you must have to create "common user"
under CDB$ROOT with "c##" or "C##" beging of the user name. it is a rule)


In oracle 12c Database there are mainly two type of users

1) common users
2) local users

[ Note :  CDB = Container Database : A container is a collection of schemas, objects, and related structures in a Multitenant container Database that appears logically to an application as a separate database. Within a CDB, each container has a unique ID and name.
       
            PDB = Pluggable Database : The root and every PDB is considered a container. PDBs isolate data and operations so that from the perspective of a user or application, each PDB appears as if it were a traditional non-CDB.
          A PDB is a user-created set of schemas, objects, and related structures that appears logically to an application as a separate database. Every PDB is owned by SYS, which is a common user in the CDB, regardless of which user created the PDB.

        ROOT Container : Every CDB has one and only one root container, which stores the system metadata required to manage PDBs. All PDBs belong to the root. The name of the root is CDB$ROOT.
The root does not store user data. Thus, you must not add user data to the root or modify system-supplied schemas in the root. However, you can create common users and roles for database administration . A common user with the necessary privileges can switch between PDBs.
 ]



you have to understand the difference between them for mitigation the error.

Common Users in a CDB
======================

A common user is a database user that has the same identity in the root and in every existing and future PDB. Every common user can connect to and perform operations within the root, and within any PDB in which it has privileges.

Every common user is either Oracle-supplied or user-created. Examples of Oracle-supplied common users are SYS and SYSTEM.


Common users have the following characteristics:

    A common user can log in to any container (including CDB$ROOT) in which it has the CREATE SESSION privilege.


    The name of every user-created common user must begin with the characters c## or C##. (Oracle-supplied common user names do not have this restriction.)


    The names of common users must contain only ASCII or EBCDIC characters.


    Every common user is uniquely named across all containers.

  
    The schemas for a common user can differ in each container.




Local Users in a CDB
============================

A local user is a database user that is not common and can operate only within a single PDB. Local users have the following characteristics:

    A local user is specific to a particular PDB and owns a schema in this PDB.

    A local user on one PDB cannot log in to another PDB or to the root
   
    The name of a local user must not begin with the characters c## or C##.

    The name of a local user must only be unique within its PDB.

    Whether local users can access objects in a common schema depends on their user privileges.
   

=======================================
======================================
In details with live examples
======================================
=========================================


SQL>
SQL>
SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> select * from v$version ;

BANNER                                                                             CON_ID
------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production            0
PL/SQL Release 12.1.0.1.0 - Production                                                  0
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                  0

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;
create user halim identified by halim
            *
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
SQL>
SQL>
SQL> create user halimdb identified by halimdb container=current;
create user halimdb identified by halimdb container=current
                                  *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT
SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim container=ALL ;
create user halim identified by halim container=ALL
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL>

===========================================
===========================================
           Solution
(you must have to create "common user"
under CDB$ROOT with "c##" or "C##" beging of the user name.
 it is a rule)
===========================================
============================================
SQL>
SQL> create user c##halim identified by halim ;

User created.

SQL>
SQL>
SQL> create user c##halim_1 identified by halim container=ALL ;

User created.

SQL>
SQL>
SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
  2    AS CUR_CONTAINER FROM DUAL;

CUR_CONTAINER
------------------------------------------------------------------------------------------
--------------------------------------------------------
CDB$ROOT

SQL>
SQL>
SQL>
SQL>
SQL> grant create session to c##halim ;

Grant succeeded.

SQL>
SQL>
SQL> conn c##halim/halim
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL>
SQL>
SQL>
SQL> conn c##halim/halim@halim12c
Connected.
SQL>
SQL>
SQL> conn sys/sys@halim12c as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> SELECT NAME, PDB FROM V$SERVICES
  2    ORDER BY PDB, NAME;

NAME                           PDB
------------------------------ ------------------------------
HALIM12C                       CDB$ROOT
HALIM12CXDB                    CDB$ROOT
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
pdb_hr                         PDB_HR

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> alter session set container=pdb_hr ;

Session altered.

SQL>
SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')
  2    AS CUR_CONTAINER FROM DUAL;

CUR_CONTAINER
------------------------------------------------------------------------------------------
--------------------------------------------------------
PDB_HR

SQL>
SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;
create user halim identified by halim
                                *
ERROR at line 1:
ORA-01109: database not open


SQL>
SQL>
SQL> startup
ORA-24543: instance startup or shutdown not allowed in pluggable database
SQL>
SQL>
SQL> alter database pdb_hr open ;

Database altered.

SQL>
SQL>
SQL>
SQL> create user halim identified by halim ;

User created.

SQL>
SQL>
SQL>
SQL> grant dba to halim ;

Grant succeeded.

SQL>
SQL>
SQL>