ORA-65096 is raised when CREATE USER or CREATE ROLE runs in the root container of a multitenant database, CDB$ROOT, with a name that lacks the C## prefix. Users created in the root are common users, which exist in every container, and Oracle requires the prefix for them. Most of the time the user was meant to be a local user of a pluggable database, and the session was simply in the wrong container.
Reproduce the Error
Connected as SYSDBA, the session starts in the root:
Example:
sqlplus -s / as sysdba <<'SQL' show con_name create user demo_app identified by "Demo_Pass_26"; SQL
Output:
CON_NAME
------------------------------
CDB$ROOT
create user demo_app identified by "Demo_Pass_26"
*
ERROR at line 1:
ORA-65096: common user or role name must start with prefix C##Fix: Create the User in the Pluggable Database
Switch to the pluggable database, or connect to its service, and create a local user there. Only a user for all containers takes the C## prefix:
Example:
sqlplus -s / as sysdba <<'SQL' -- a local user belongs in a pluggable database alter session set container = FREEPDB1; show con_name create user demo_app identified by "Demo_Pass_26"; drop user demo_app; -- a common user, for all containers, needs the C## prefix alter session set container = CDB$ROOT; create user c##demo_admin identified by "Demo_Pass_26" container = all; drop user c##demo_admin; SQL
Output:
Session altered. CON_NAME ------------------------------ FREEPDB1 User created. User dropped. Session altered. User created. User dropped.
- In FREEPDB1, DEMO_APP is created as a local user.
- In the root, C##DEMO_ADMIN is created as a common user, with CONTAINER = ALL.
- Both test users are dropped again.
Things to Know
- SHOW CON_NAME, or SYS_CONTEXT('USERENV', 'CON_NAME'), tells which container the session is in.
- Applications should connect to the pluggable database's service, not to the root, so their users are local users.
- The hidden parameter _ORACLE_SCRIPT, sometimes suggested to avoid the prefix, is for Oracle's own scripts and should not be used for application users.
Related Guides
- How to Create Users with Profiles in Oracle
- How to Fix ORA-12514: Listener Does Not Currently Know of Service Requested in Oracle
Conclusion
ORA-65096 means a user was created in the root container without the C## prefix. Create application users in the pluggable database, and use C## names only for common users that every container needs.
