ORA-65096: invalid common user or role name

Question

Hi I've tried to create a new user in Oracle 18c XE, but I get

ORA-65096: invalid common user or role name error when writing

create user student identified by "student";

I've tried to change the container to PDB by

SQL> alter session set container =PDB;

as I've understood that you should set that when trying to create a local user but I get the following error:

ORA-65011: Pluggable database PDB does not exist.

Do you have any idea how could I create a new user with all privileges from the command prompt?

Answer 1

A user cannot be created on a container for a DB with vers. 12c+.

So, need to alter as you did, but should display which pluggable databases are available :

SQL> select name, pdb from v$services order by pdb, name;

NAME                                                          PDB
-----------------------------------------------           ----------
SYS$BACKGROUND                                            CDB$ROOT
SYS$USERS                                                 CDB$ROOT
pdb1                                                      PDB1
pdb2                                                      PDB2

and check out the container by

SQL>show con_name

CON_NAME
——————————
CDB$ROOT

and check for the pluggable databases

SQL> select name,open_mode  from v$pdbs;

NAME                      OPEN_MODE
-------------------       ----------
PDB$SEED                  READ ONLY
PDB1                      MOUNTED
PDB2                      MOUNTED

change container to a pluggable database

SQL> alter session set container=pdb1;
Session altered.

and open it

SQL> alter pluggable database pdb1 open;

Now, you can apply

SQL> create user student identified by student;

as an example.

Answer 2

First run the following command:

SQL> alter session set "_ORACLE_SCRIPT"=true;

After that, create the user:

SQL> create user student identified by student;