ORACLE12-invalid create user

dbsa@dbsa-X406UA:~/Downloads$ ssh root@172.16.10.140
The authenticity of host '172.16.10.140 (172.16.10.140)' can't be established.
ECDSA key fingerprint is SHA256:w8Do81sv2Cc0FRTdvVk2Z3M6Qcr1Sc2zbHBddBLNcaI.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '172.16.10.140' (ECDSA) to the list of known hosts.
root@172.16.10.140's password:  
Activate the web console with: systemctl enable --now cockpit.socket

This system is not registered to Red Hat Insights. See https://cloud.redhat.com/
To register this system, run: insights-client --register

Last login: Mon Mar 29 17:25:00 2021
[root@goldapi ~]#

[oracle@goldapi ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 29 17:27:50 2021

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

SQL> create user picktolight identified by pick_to_light;^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D^[[D
^[[A^[[B
 2   
SQL> create user goldapi identified by goldapi;
SP2-0640: Not connected
SQL> connect /as sysdba;
Connected.
SQL> create user goldapi identified by goldapi;
create user goldapi identified by goldapi
           *
ERROR at line 1:
ORA-65096: invalid common user or role name


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@goldapi ~]$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 29 17:30:15 2021

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

SQL> connect /as sysdba;
Connected.
SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> create user goldapi identified by goldapi;               
create user goldapi identified by goldapi
           *
ERROR at line 1:
ORA-65096: invalid common user or role name


SQL> show user;
USER is "SYS"
SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> create user goldapi identified by goldapi;

User created.

SQL>

 

Answer:  First, read my notes on common vs. local_user ID's. 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. Oracle makes a distinction in multitenant databases for local vs. "common" user ID's:

• COMMON USERS:  Common user ID's must always be created at the CDB level and start with a C## or c## prefixes.
  
  
•  LOCAL USERS:  Local users can only be created at the PDB layer; also must use the CONTAINER clause set to CURRENT for the user to be created.
  

Oracle Training from Don Burleson