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>

Identity column in oracle 12c Database

Identity column in oracle 12c Database
-----------------------------------------------

Now in Oracle Database 12c Release 1 database has ANSI-compliant IDENTITY column
like Microsoft SQL Server's Identity column for auto increment number generation.

So, now migration from Microsoft SQL Server's to oracle database will be easier
in case of IDENTITY COLUMN.


There are three option for identity  when we will create a table.

1)  by default : BY DEFAULT If you specify BY DEFAULT, then Oracle Database uses
                 the sequence generator to assign a value to the column by default,
                 but you can also explicitly assign a specified value to the column.

2)  by default on null : If you specify ON NULL, then Oracle Database uses the
                         sequence generator to assign a value to the column when a subsequent
                         INSERT statement attempts to assign a value that evaluates to NULL.

3)  always  : ALWAYS If you specify ALWAYS, then Oracle Database always uses the
              sequence generator to assign a value to the column. If you attempt to
              explicitly assign a value to the column using INSERT or UPDATE,
              then an error will be returned. This is the default.


In details explanation is below
=================================


[oracle@halim12c] $ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 11 18:32:33 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> conn sys@halim12c as sysdba
Enter password:
Connected.
SQL>
SQL> col banner format a78
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                                                              0
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>
SQL>
SQL> select host_name from v$instance ;

HOST_NAME
----------------------------------------------------------------
halim-srv.localdomain

SQL>
SQL> select name from V$database ;

NAME
---------
HALIM12C

SQL>
SQL>
SQL>
               =============================================================
                       Identity column "generated by default" example
                ===========================================================
              =================================================================
SQL>
SQL>
SQL>
SQL> create table t_identity_def (id number generated by default as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim');

1 row created.

SQL>
SQL> insert into t_identity_def (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def ;

Table truncated.

SQL>
SQL>
SQL> select * from t_identity_def ;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def ;

        ID NAME
---------- ------------------------------
         3 halim2

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> insert into t_identity_def (id,name) values (null,'halim');
insert into t_identity_def (id,name) values (null,'halim')
                                             *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYS"."T_IDENTITY_DEF"."ID")

SQL>
SQL>


               =============================================================
                  Identity column "generated by default on null" example
                ===========================================================
              =================================================================


SQL>
SQL> create table t_identity_def_null (id number generated by default on null as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL>
SQL> select * from t_identity_def_null ;

no rows selected

SQL>
SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim');

1 row created.

SQL>
SQL> insert into t_identity_def_null (id,name) values (null,'halim1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (id,name) values (66,'halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL> commit ;

Commit complete.

SQL>
SQL> select * from t_identity_def_null ;

        ID NAME
---------- ------------------------------
         1 halim
         2 halim1
         3 halim1
        66 halim1
         4 halim1

SQL>
SQL>
SQL> truncate table  t_identity_def_null ;

Table truncated.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

no rows selected

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim1');

1 row created.

SQL>
SQL>
SQL> insert into t_identity_def_null (name) values ('halim2');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_def_null;

        ID NAME
---------- ------------------------------
         5 halim1
         6 halim1
         7 halim2

SQL>
SQL>
SQL>

                =============================================================
                       Identity column "generated always" example
                ===========================================================
              =================================================================

SQL>
SQL>
SQL>
SQL> create table t_identity_always (id number generated always as identity, name varchar2(20));

Table created.

SQL>
SQL>
SQL> insert into t_identity_always (name) values ('always');

1 row created.

SQL>
SQL> insert into t_identity_always (name) values ('always1');

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select * from  t_identity_always;

        ID NAME
---------- ------------------------------
         1 always
         2 always1

SQL>
SQL>
SQL> insert into t_identity_always (id,name) values (3,'always1');
insert into t_identity_always (id,name) values (3,'always1')
                               *
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column


SQL>
SQL>
SQL>
SQL>