In four steps will fix the issue
Error at Command Line:16 Column:13
Error report:
SQL Error: ORA-65096: invalid common user or role name
Determine the current container
SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM dual;
SYS_CONTEXT(‘USERENV’,’CON_NAME’)
——————————————————
CDB$ROOT
——————————————————
CDB$ROOT
List all the pluggable databases
select name,open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
PDBORCL MOUNTED
—————————— ———-
PDB$SEED READ ONLY
PDBORCL MOUNTED
PDB need to open read write atleast once to inegrate into CDB
ALTER PLUGGABLE DATABASE PDBORCL OPEN READ WRITE;
pluggable database is altered
Now PDB is ready to be used we just need to change the current session
ALTER SESSION SET CONTAINER = PDBORCL
Use this query to make sure you are in right container
SELECT Sys_context (‘USERENV’, ‘CON_NAME’)FROM dual;
CREATE USER sqljunkie IDENTIFIED BY sqljunkie DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
user SQLJUNKIE created.
GRANT CREATE PROCEDURE
, CREATE session
, CREATE TABLE
, CREATE type
, CREATE VIEW
, CREATE SYNONYM
, CREATE TRIGGER
, RESOURCE TO sqljunkie;
Once we opened database it runs as its own service we can verify this
if you are planning to connect using TNSnaming service make sure you have something like below in tnsnames.ora
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
there we go
Sqljunkieshare 