Fix: invalid common user or role name ORACLE 12C

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
List all the pluggable databases

select name,open_mode from v$pdbs;
NAME                           OPEN_MODE
—————————— ———-
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
lsnctl service modified
 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)
)
)
pdb connection
there we go
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s