error: ORA-65096: invalid common user or role name in oracle
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. For example, if you used the default 19c installation settings, you should login to ORCLPDB (the PDB) instead of ORCL (the CDB).
DANGER - If you insist on creating users the wrong way, follow the steps below.
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 "_ORACLE_SCRIPT"=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, run this command before creating the user:
alter session set "_ORACLE_SCRIPT"=true;
I found the answer here
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> ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger;
sqlplus scott/tiger@pdborcl
SQL> show user;
USER is "SCOTT"
To show the PDBs and connect to a pluggable database from root container:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCLPDB READ WRITE NO
SQL> alter session set container = ORCLPDB;
Session altered.
SQL> 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.
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
SQL> alter session set "_ORACLE_SCRIPT"=true;
SQL> create user sec_admin identified by "Chutinhbk123@!";
If your goal is to create an Oracle user and then use it to do stuff with your DB you probably want to consider doing this:
- log as sysdba
- create a pdb (a.k.a. pluggable database)
- create a user for your pdb
What follows assume you are working with a db in localhost, if not simply change "localhost" with your db uri.
Sample code
Log as sysdba:
$ sqlplus sys/<your_admin_pws>@localhost as sysdba
then execute this:
create pluggable database MYDATABASE
admin user Scott identified by tiger;
file_name_convert = ('/pdbseed/', '/mydatabase/')
;
alter pluggable database MYDATABASE open;
then you may want to grant some permission to user Scott: log out from @localhost and log back in as sysdba to your new db
$ sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba
and grant Scott whatever permission you want, e.g.
grant connect to Scott;
grant create view to Scott;
grant create table to Scott;
grant create trigger to Scott;
now you are good to go: you have an empty database instance and a user. Just log in by doing
$ sqlplus Scott/tiger@localhost/MYDATABASE
Bonus
I had problem with tablespaces and quotas after that.
Log with sqlplus sys/<your_admin_pws>@localhost/MYDATABASE as sysdba
You can list all tablespaces with
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
You can create a new tablespace with
CREATE TABLESPACE TABLESPACENAME DATAFILE 'tablespace_datafile.dat' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M;
You can drop your broken tablespaces with
Drop tablespace TABLESPACENAME including contents and datafiles;
You can grant "access" to the tablespace to Scott with
alter user Scott quota unlimited on TABLESPACENAME;
N.B. quota unlimited is probably a bad practice, check oracle docs on how to limit user's quota