I am currently trying to set up a new local user in my Oracle database using the standard CREATE USER command, but I keep hitting the ORA-65096: invalid common user or role name error. I am logged in as SYSDBA, yet the system refuses the username I provide. Does this have something to do with the Container Database (CDB) and Pluggable Database (PDB) architecture? What is the correct naming convention or session setting required to successfully create a user in this environment?
3 answers
The ORA-65096 error occurs because you are attempting to create a user in the Container Database (CDB$ROOT) without following the strict naming rules for "Common Users." In Oracle's multitenant architecture, a common user exists across all containers and must be prefixed with C## or c## (e.g., CREATE USER c##myuser IDENTIFIED BY password;). Alternatively, if you intended to create a local user for a specific application, you must first switch your session to the appropriate Pluggable Database (PDB) using ALTER SESSION SET CONTAINER = your_pdb_name;. Once inside a PDB, the C## prefix is no longer required, and you can create users with standard names as you did in older versions of Oracle.
Are you trying to create a global admin that can see all databases, or are you just trying to get a simple schema set up for a specific project or application?
The quickest fix is adding C## to your username. Oracle requires this prefix for any user created while the container is set to the root level to distinguish them from local PDB users.
Pamela is spot on. I’ve seen so many developers get stuck here because they forget they are connected to the root container by default. Always check your con_name before running DDL commands!
Timothy, I’m just trying to set up a quick lab environment for a school project. If you are in a rush and don't want to deal with the C## prefix or switching containers, there is a "hidden" workaround. You can run ALTER SESSION SET "_ORACLE_SCRIPT"=true; before your create command. This bypasses the naming restriction for that specific session, but be careful—it’s technically meant for Oracle’s internal scripts and isn’t recommended for a production enterprise environment where security standards are high!