Solve Error: ORA-65096 in 12c

In 12c database when we try to create any user we commonly see the error “Error: ORA-65096” .

We can solve this problem by setting up the undocumented hidden parameter i.e. “_ORACLE_SCRIPT” to true.  In other way we have to user the prefix by adding “C##” or “c##” before the username in container database but by enabling the hidden parameter from logging as SYS user we will be able to create the user without adding the prefix.

Here is a small sample how to create the user when we face error 65096.

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 19:04:49 2016

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL>create user sloba identified by ****** ;

Once you hit enter you will see the below error:


<pre>Error starting at line : 1 in command -
create user sloba identified by ******
Error report -
SQL Error: ORA-65096: invalid common user or role name
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
           that was 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.
Error starting at line : 3 in command -

 

To solve this error enable the hidden parameter to “True”:

SQL>alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL>reate user sloba identified by ***** ;

User SLOBA created.

SQL>grant connect , resource to sloba;

Grant succeeded.

Verify by logging into the database with the new user:

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 19:04:49 2016

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> conn sloba
Enter password:
Connected.
SQL>

Thank you for reading my blog ,please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s