How To Fix ORA-65096: Invalid Common User Or Role Name Error?

Does your database frequently showing invalid common user or role name error whenever you try to open your Oracle Database?

No need to deal with this ORA-65096: Invalid Common User Or Role Name error anymore. As today in this article, I am going to share every point of detail on Oracle ORA-65096 error. Also, try some easy fixes to resolve this Oracle Error ORA-65096.

What’s This Oracle Error ORA-65096 Means?

Oracle database is one of the most common databases accessed by most of us. It is widely used because of its user credibility and usability and has large advantages over other database software. Almost every database stores important and sensitive data of belonging organization that is very crucial for the development and existence of the enterprise.

Therefore it becomes very much important that the information is protected and secured. Likewise, if your Oracle database is sensitive, personal and it needs to be kept confidential then it is important that one should know the importance of security in database files thus making them secure. If you are an Oracle database user then you should be very well familiar with the ORA-65096: invalid common user or role name.

The error ORA-65096: invalid common user or role name mostly occurs due to the creation of another database or when the user is not able to access its account due to inaccessibility.

SELECT, INSERT, UPDATE, and DELETE are used to perform CRUD operation in the Oracle database. What will you do if you get this error while running in SQL script?

In such a situation firstly you have to know what this Oracle trying to say here. Invalid identifier means the column name entered is either missing or invalid; this one is the most common cause of this error but not the only one. Sometimes it occurred if you use names, which happened to be reserved word in the Oracle database. So let’s first know for what reason you got such an error message and how to resolve it.

What Are The Causes Of “ORA-65096: Invalid Common User Or Role Name” Error?

The main reason for the ORA-65096 error is the creation of a user (Common user) under the root container. Oracle has two types of users: Common users and Local users.

Common users are those belonging to CBD’s as well as current and future PDBs. They can perform the operation in Container or Pluggable according to Privileges assigned.

Local users are purely database that belongs to only single PDB. This user may have administrative privileges but this only belongs to that PDB.

// Let’s take an example in which a common user in container root is being created.

SQL> show con_name

CON_NAME ——————————

CDB$ROOT

SQL> create user ryan identified by ryan; create user ryan identified by ryan * ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> create user Ryan identified by Ryan container=current; create user Ryan identified by Ryan container=current * ERROR at line 1:

ORA-65049: the creation of a local user or role is not allowed in CDB$ROOT

SQL> create user Ryan identified by Ryan container=all; create user Ryan identified by Ryan container=all * ERROR at line 1:

ORA-65096: invalid common user or role name

//If you wish to create a common user under CDB$ROOT than create user start with C## and c##, as follows:
Note:

• Common user will be created under root container only.
• The current container must be set to CDB$ROOT.

SQL> create user C##ryanidentified by Ryan;

User-created.

SQL> create user c##ryanidentified by ryancontainer=all;

User-created.

OR

// Creating a local user in PDB:

SQL> alter session set container=sales;

Session altered.

SQL> sho con_name

CON_NAME ——————————

SALES

SQL> create user test identified by test;

User created.

“ORA-65096: invalid common user or role name” attempts to create a common user or role with a name that is not valid for common users. In addition to usual rules for user and role names, common user and role names must start with C## or c## and consist only of ASCII characters.

Message:

Certain geoprocessing tools are when executed on an Oracle 12c database the following error is encountered:
“ORA-65096: invalid common user or role name“

Below is an example of error messages received using the ‘Create Enterprise Geodatabase’ tool:

“Error creating geodatabase admin user. [ERROR: Failed to create a new Oracle user side (-51).”

“Error: Underlying DBMS error (-51).
Extended error code: (65096)”

“ORA-65096: invalid common user or role name“

“Failed to execute (CreateEnterpriseGeodatabase).”

This error is encountered when running the following tools:

• Create Enterprise Geodatabase
• Create Database User
• Create Role

Thus the error occurs because of Oracle instance being a container database.  Databases can be created as a container database with associated databases. Currently, Oracle is designed in such a way that it supports users at the container level only for administrative tasks. Due to this design, enterprise geodatabases do not function at the container level.

Method 1# Assign Connection String To Pluggable Database 

Instead of providing the connection string to the container database, provide a connection string to one of the pluggable databases. To check if a connection is to a container database run the following query in Oracle:

Code:

SELECT CDB FROM V$DATABASE;

If the result of this query is ‘YES’, run the following query to determine if there are any pluggable databases within the container:

Code:
SELECT NAME, OPEN_MODE FROM V$PDBS WHERE CON_ID > 2;

If there are pluggable databases available, ask the DBA which pluggable database may be used to create an enterprise geodatabase.

Method 2# Repair Corrupt Oracle Files

You might be getting this oracle Invalid Common User Or Role Name error due to the corruption of your Oracle database files. So without wasting any more time just try this professional recommended solution.

Oracle File Repair Tool which is a powerful and comprehensive data recovery software which is designed to recover corrupted or damaged database file. In case a sudden database fails, then this repair tool meets this requirement perfectly. It searches all Oracle database which is present in the system and also shows the preview of recoverable database objects.

This software restores data from both database and backup files. This software saves recovered data as Transact-SQL script. Oracle File Repair Tool is easy to use and it does not require any special skills.

Steps To Repair Oracle Database

Complete instructions on how to use and work with Oracle File Repair tool is explained below. So, just go through it once before doing it practically.

 Step 1: Search the initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases in your computer. Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane. Step 3: Click an object to see its preview.  

Step 4: Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.