How to Fix ORA-65096: Common User or Role Name Must Start with Prefix C## in Oracle

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.