Oracle 12c Common User & Local User
Common user must be created in CDB only.
When we create a common user must give C## as prefix
The user is present in all containers(CDB$ROOT and all PDB)
Local user can only created at the PDB.
The same username can be created in multiple PDB and they are unrelated.
SQL> show con_name
create user sree identified by oracle
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> !oerr ora 65096
65096, 00000, “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 consist only of ASCII characters, and must contain the prefix
// specified in common_user_prefix parameter.
// *Action: Specify a valid common user or role name.
It agains the rule because only common user allowed to create in CDB. If any reason we need to create the local user in CDB we use undocumented parameter _oracle_script=true at system level.
We connected to common user with the create user privilege.
The current container must be the root container.
The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
The username must be unique across all containers.
The Default table space, Temporary Table space, Quota and Profile, must all reference objects that exist in all containers.
You can either specify the container=all clause, or omit it, as this is the default setting when the current container is the root.
Common user with container clause.
Create Local User
You must be connected to a user with the create user privilege
The username for the local user must not be prefixed with “c##”.
The username must be unique within the PDB.
You can either specify the container=current clause, or omit it, as this is the default setting when the current container is a PDB.
Switching container for session
SQL> alter session set container=pram;
Connect as a User
]$ sqlplus essvee@pram
Last Successful login time: Tue Jun 12 2018 12:24:14 +05:30
Oracle Database 12c Enterprise Edition Release 18.104.22.168.0 – 64bit Production
USER is “ESSVEE”