CODEWITHSUNDEEP
oracle-databasedatabase-connectionoracle12c
jase
jase

Reputation: 29

Unlock HR user in Oracle 12C

I'm trying to unlock HR user; I've tried all the possible way, but I'm not able to achieve it.

Below are the steps I've followed in the attempt to achieve that:

Step 1: Updated my tnsname.ora file

PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )

Step 2: Switch the container from CBD$ROOT to PDBORCL

Step 3: ALTER SESSION SET CONTAINER = pdborcl;

Step 4: ALTER PLUGGABLE DATABASE open;

Step 5: ALTER USER hr IDENTIFIED BY hr ACCOUNT unlock;

Step 6: conn hr/hr @pdborcl;

Up to step 5, I get everything working well as expected, but on step 6, I get this error:

ORA-01045: hr user lacks create session privileges; logon denied

I try to grant privileges by logging as below: 

connect sys/password as sysdba;

grant create session to hr

The result: grant succeeded.

Now again when I try to connect to hr schema running

conn hr/hr @pdborcl;

I get the same previous error again.

What can account for this?

When I set SID = pdborcl from SQL Developer, I get this error:

ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

What am I doing wrong and how can I resolve these errors?

Upvotes: 1

Views: 5793

Answers (2)

Naveen kumar
Naveen kumar

Reputation: 1725

You need to add one more entry in tnsnames.ora file for pdb.

PDBORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl1)
    )
  )

Upvotes: 0

Rahul Malireddy
Rahul Malireddy

Reputation: 31

once you connect using sysdba you need to set the session to pdb and then grant HR user create session access.

conn / as sysdba alter session set container=pdborcl; grant create session to HR; exit sqlplus hr/hr@pdborcl connected.

Upvotes: 1

Related Questions