error ORA-65096 invalid common user or role name in oracle

Question

I just installed Oracle  and it was missing the Scott schema  So i am trying to generate it myself  I got the sql script of Scott schema  but when i try to run the query  create user Scott identified by tiger    it displays the following error   ORA-65096  invalid common user or role name in oracle   Basically it is not allowing me to create a user Scott  Why is that  and how can I fix my problem

User · Answer

I just installed oracle11g      ORA-65096  invalid common user or role name in oracle   No  you have installed Oracle 12c  That error could only be on 12c  and cannot be on 11g    Always check your database version up to 4 decimal places   SELECT banner FROM v version WHERE ROWNUM   1    Oracle 12c multitenant container database has    a root container CDB  and or zero  one or many pluggable databases PDB      You must have created the database as a container database  While  you are trying to create user in the container  i e  CDB ROOT  however  you should create the user in the PLUGGABLE database   You are not supposed to create application-related objects in the container  the container holds the metadata for the pluggable databases  You should use the pluggable database for you general database operations  Else  do not create it as container  and not use multi-tenancy  However  12cR2 onward you cannot create a non-container database anyway    And most probably  the sample schemas might have been already installed  you just need to unlock them in the pluggable database   For example  if you created pluggable database as pdborcl   sqlplus SYS password PDBORCL AS SYSDBA  SQL gt  ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger   sqlplus scott tiger pdborcl  SQL gt  show user  USER is  SCOTT    To show the PDBs and connect to a pluggable database from root container   SQL gt  show con name  CON NAME ------------------------------ CDB ROOT  SQL gt  show pdbs      CON ID CON NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB SEED                       READ ONLY  NO          3 ORCLPDB                        READ WRITE NO  SQL gt  alter session set container   ORCLPDB   Session altered   SQL gt  show con name   CON NAME ------------------------------ ORCLPDB   I suggest read  Oracle 12c Post Installation Mandatory Steps    Note  Answers suggesting to use the  ORACLE SCRIPT hidden parameter to set to true is dangerous in a production system and might also invalidate your support contract  Beware  without consulting Oracle support DO NOT use hidden parameters

User · Answer

SQL gt  alter session set  quot  ORACLE SCRIPT quot  true    SQL gt  create user sec admin identified by  quot Chutinhbk123   quot

User · Answer

Might be  more safe alternative to   ORACLE SCRIPT  true is to change   common user prefix  from C   to an empty string  When it s null - any name can be used for common user  Found there   During changing that value you may face another issue - ORA-02095 - parameter cannot be modified  that can be fixed in a several ways  based on your configuration  source    So for me worked that   alter system set  common user prefix       scope spfile

User · Answer

Create user dependency upon the database connect tools  sql plus SQL gt  connect as sysdba  Enter user-name  sysdba Enter password  Connected  SQL gt  ALTER USER hr account unlock identified by hr  User altered  then create user on sql plus and sql developer

User · Answer

In Oracle 12c and above  we have two types of databases    Container DataBase  CDB   and Pluggable DataBase  PDB      If you want to create an user  you have two possibilities     You can create a  container user  aka  common user   Common users belong to CBDs as well as to current and future PDBs  It means they can perform  operations in Container DBs or Pluggable DBs according to assigned privileges   create user c  username identified by password  You can create a  pluggable user  aka  local user   Local users  belong only to a single PDB  These users may be given administrative privileges  but only for that PDB inside which they exist  For that  you should connect to pluggable datable like that   alter session set container   nameofyourpluggabledatabase   and there  you can create user like usually   create user username identified by password    Don t forget to specify the tablespace s  to use  it can be useful during import export of your DBs  See this for more information about it https   docs oracle com database 121 SQLRF statements 8003 htm SQLRF01503

User · Answer

99 9  of the time the error ORA-65096  invalid common user or role name means you are logged into the CDB when you should be logged into a PDB  But if you insist on creating users the wrong way  follow the steps below  DANGER Setting undocumented parameters like this  as indicated by the leading underscore  should only be done under the direction of Oracle Support  Changing such parameters without such guidance may invalidate your support contract  So do this at your own risk  Specifically  if you set  quot  ORACLE SCRIPT quot  true  some data dictionary changes will be made with the column ORACLE MAINTAINED set to  Y   Those users and objects will be incorrectly excluded from some DBA scripts  And they may be incorrectly included in some system scripts  If you are OK with the above risks  and don t want to create common users the correct way  use the below answer   Before creating the user run  alter session set  quot  ORACLE SCRIPT quot  true     I found the answer here

[oracle] error: ORA-65096: invalid common user or role name in oracle

Examples related to oracle

Examples related to oracle12c