ORA 65096 Invalid Common User or Role Name - Oracle 12C

This post explains the reason for the weird error – ORA 65096 – Invalid Common User or Role Name that you may encounter in Oracle 12C while creating a new user / schema and offers a way to solve it. You would need a working version of Oracle 12C database and needless to say, encountered this error while you tried creating a schema with some of the commands you used before, as a reference see below

CDB$ROOT@ORCL> create user hr identified by hr 
  2  / 
create user hr identified by hr 
            * 
ERROR at line 1: 
ORA-65096: invalid common user or role name 

OERR Lookup - Error Code 65096

If you do a lookup for the error code 65096 using OERR utility, you can find the explanation as shown below

[oracle@localhost ~]$ oerr ora 65096 
65096, 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. 
// 

This is because of the multitenant architecture introduced in Oracle 12c. You need to have an understanding on multitenant container database (CDB) and Pluggable databases (PDB) to appreciate the need for this error. In the step above, an attempt was made to create an user in CDB to manage multiple PDBs, and for some reason Oracle does not like the syntax that has been provided.

Solution - For Creating a CDB User

If the intention is to create an user in CDB than prefix “C##” to the user name as shown below;

CDB$ROOT@ORCL> create user c##hr identified by hr 
  2  / 
User created. 
CDB$ROOT@ORCL> 

Solution - For Creating a PDB User

Or, if you want to create an user in one of the PDBs from a CDB user, you need to switch to the PDB first and then create an user. This is shown below;

CDB$ROOT@ORCL> show con_name 
CON_NAME 
------------------------------ 
CDB$ROOT 
CDB$ROOT@ORCL> select pdb from v$services; 
PDB 
------------------------------ 
PDB1 
CDB$ROOT 
CDB$ROOT 
CDB$ROOT 
CDB$ROOT 
CDB$ROOT@ORCL> alter session set container=PDB1 
  2  / 
Session altered. 
CDB$ROOT@ORCL> create user hr2 identified by hr2 
  2  / 
User created. 
CDB$ROOT@ORCL> show con_name 
CON_NAME 
------------------------------ 
PDB1 
CDB$ROOT@ORCL> 

Switching to a PDB is done via alter session command, where we set the container to the name of the PDB where a user account needs to be created. The command to create an user will work normally following that.

Bulk Insert Skip / Ignore Duplicate Rows Oracle Example

In this short tutorial, we will see how to ignore duplicate records from a table during a bulk insert operation in Oracle. The example works on 11g Oracle databases and above and utilizes the hint ignore_row_on_dupkey_index . First things first, why do we need this tutorial?

The problems with Bulk Inserts

Let us say you have a table with a unique key and millions of row. And you perform a bulk insert from another table, and you have a scenario where some of the records inserted are already available in your original table. During such cases, Oracle will reject your insert and throw a unique key constraint violation error. Just because you have a few duplicate rows in your insert set, your complete insert will fail. You will then find the duplicate rows through some means, and ignore them in your insert which is a painful process. This hint simplifies your insert greatly and we will see how it does that, in the following section.

Step -1 : Create a Test Table / Insert Some Rows.

Let us create a test table to explain this and add some rows into it. Execute the statements provided below and have your table ready.

CREATE TABLE TEMP_1
(
MY_ID NUMBER PRIMARY KEY,
MY_TEXT VARCHAR2(10)
)
/
INSERT INTO TEMP_1 VALUES (1,'A')
INSERT INTO TEMP_1 VALUES (2,'B')
INSERT INTO TEMP_1 VALUES (3,'C')
INSERT INTO TEMP_1 VALUES (4,'D')
INSERT INTO TEMP_1 VALUES (5,'E')
/
COMMIT
/

There is a unique key on column MY_ID.

Step-2: Bulk Insert - without Hint - Standard Method

Let us say you attempt to make an insert as per below.

INSERT INTO TEMP_1
SELECT ROWNUM,'A' FROM ALL_OBJECTS WHERE ROWNUM <20

You will get the following exception on  the screen: ORA-00001: Unique Constraint Violated

There are so many websites / forums that discusses how you can work around this problem by detecting duplicates before the insert. Instead of reinventing them here, let us go to the new hint based solution direct.

New Approach - Using ignore_row_on_dupkey_index for Bulk Insert

The new approach would be to use the hint ignore_row_on_dupkey_index during the bulk insert. You should also pass the table name and the index name as parameters inside the hint. The new SQL statement is therefore;

INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(TEMP_1,SYS_C0011527) */ INTO TEMP_1
SELECT ROWNUM,'A' FROM ALL_OBJECTS WHERE ROWNUM <20

When you run this statement, it will insert 14 rows and will not touch the existing rows (i.e.1 - 5 ). This is a big time saving feature from Oracle. It prevents the collision of rows during insert and takes the rows that don't complain on the target tables.

Give your try on this hint, and post your experience.