[Resolved]Oracle Error ORA-65096 While Performing Operation That Creates A New User Or RoleFile Repair Tool Blog

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 organisation that are 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. This error doesn’t matter what version of Oracle is being used. 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 Oracle database. What will you do if you get this error while running in SQL script? In such 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. Some time it occurred if you use names, which happened to be reserved word in Oracle database. So let’s first know by what reason you got such error message and how to resolve it.

You May Also Like: How to Repair ORA-01578: Oracle Data Block Corruption

Reason for “ORA-65096: invalid common user or role name” error

The main reason for ORA-65096 due to the creation of user (Common user) under 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 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: creation of 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 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” attempt 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 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.

You May Also Like: How to Fix Oracle Error ORA-6413: “connection not open”

Workaround for ORA-65096: invalid common user or role name

 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.

Oracle Repair Tool- Best tool to repair corrupt oracle files

Oracle Repair Tool which is powerful and comprehensive data recovery software which is designed to recover corrupted or damaged database file. In case sudden database fails, then this Repair Tool meets this requirement perfectly. This software searches all Oracle databases which is present in the system and also shows 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 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 Repair tool is explained below. So, just go through it once before doing it practically.

 Step 1: Search 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.