Monday, 15 June 2015

user creation in oracle 12c

As salamo alaikum wa rahmatullah

Question:  I am trying to create a user in Oracle 12c and I don't give a rat's butt about pluggable databases.  When I try to create a user with the c## prefix I get the dreaded ORA-65096 error:

create user fred identified by flintstone;
ORA-65096: invalid common user or role name

I don't care if the user ID is used as a pluggable database, I just want to create a user named fred without being required to add the c## prefix (create user c##fred).
How do I fix the ORA-65096 error and create my use without a container database c## prefix?
Answer:  The oerr utility is not at all helpful for the ORA-65096 error:
ORA-65096: 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.
The solution to the ORA-06596 is to set a hidden parameter "_oracle_script".  When you set the undocumented (hidden) parameter "_oracle_script"=true  you can create the fred user without a C## in from of the user ID.  However, this user will not used useful in a pluggable/container database:
connect system/manager as sysdba
alter session set "_ORACLE_SCRIPT"=true;
create user fred identified by flintstone;
grant dba to pubs;
connect fred/flintstone
Always consult Oracle support before using any hidden Oracle parameters. 

Mohammad Shahnawaz
Passion for Oracle