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?

Always

Default identifier is XEPDB1. Replace PDB with XEPDB1. – Cleruchy

@Cleruchy It worked, I managed to create the user and grant all privileges, but when I try to connect using that username and password, I get the following error: ORA-01017: invalid username/password; logon denied – Always

Connection string is username/password@//localhost:1521/XEPDB1 – Cleruchy

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.

Scotia

I created a new user and granted all privileges, but when I try to connect with it I get ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. – Always

I think this could reply your new issue @CristinaMoroti . Seems you'd be better considering a password without quotes. – Murrumbidgee

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;

Antipole

You almost certainly do not want to run this command. Setting this undocumented parameter might "work", but it will create the user incorrectly and in the wrong database. If you want to create a regular Oracle user, you must connect to the correct pluggable database first. – Warnock