How to unlock HR Schema in Oracle database 12c
In this post you will learn how to unlock HR schema in Oracle database 12c.
In earlier version of Oracle database (10g and 11g), it was easy to unlock the HR schema. But in Oracle 12c due to its new architecture where we have pluggable and container databases, it is little bit complex.
In Oracle database 12c the HR sample schema is placed inside the pluggable database which is created either during the installation of this version of oracle or during the creation of new container database.
Hence to unlock the HR schema in Oracle database 12c, first we require the name of that pluggable database along with the name of the service which is associated with the pluggable database.
To get all these information first we need to log on to database using SYS user.
Step 1: Open up your command prompt and log on to oracle database 12c as [\>sqlplus / as sysdba].
Unlike the previous version of Oracle database, here in Oracle database 12c when log on to the database we connect with the containers. This container could either be CBD Container or PDB pluggable database container. But by default it is always root CBD container. You can check where you are with con_name.
Step 2: Now we will find the name of the pluggable database which has our HR sample schema using v$pdbs view.
In this query along with the name of the all pluggable database, we are also retrieving their container id, so that with con_id column we can easily find out the name of the service that is associated with the pluggable database that we need.
SQL> SELECT name, con_id FROM v$pdbs;
Now we are going to use “PDBORCL” pluggable database to unlock HR schema, and using its con_id (3) we will find out the name of the service.
To find out the name of the service, we will use v$active_services view. In most of the cases Oracle creates database and their services with same name.
SQL> SELECT name FROM v$active_services WHERE con_id = 3;
Next we have to make entry in “tnsnames.ora” file to avoid TNS ORA errors.
Step 3: Make entry in “tnsnames.ora” file.
So now open your “tnsnames.ora” file, for that first you have to go to ORACLE HOME directory. Follow below path to find “tnsnames.ora” file.
Open the “tnsnames.ora” file in any notepad.
Now add PDB service entry. See below image for reference:
Rename as pdborcl.
Now save the file and exit.
Hence we just change the listener setting by adding a new service to it thus we need to reload the listener to make all services active.
Step 4: Reload the Listener.
In order to reload the listener, open up your command prompt with admin privileges (Right click on CMD and Run as administrator).
Now to reload the listener type “lsnrctl reload” and press enter.
Following will show up.
Step 5: Now again log on to the database using as SYS administrator, and check the connection name.
Now change the container from ROOT CBD to PDBORCL. Use below command:
ALTER SESSION SET CONTAINER = pdborcl;
As we know to perform any DDL operation database needs to be Open, but by default in most of the cases pluggable database are in MOUNT state.
You can check it by using v$pdbs view following:
So open the pluggable database PDBORCL, we have to use below DDL command:
SQL> ALTER PLUGGABLE DATABASE open;
And now you can see that our database is in READ WRITE mode.
Hence now we are done with all the settings and now our final step will be to unlock the HR schema.
Step 6: Unlocking the HR Schema.
We will use following command to unlock the schema:
SQL> ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;
Now to connect to HR Schema, we need to provide the service name which we created in “tnsnames.ora” file with ‘@’ sign.
SQL> conn hr/hr@PDBORCL;
Hence finally, we finished with how to unlock HR schema in Oracle database 12c. Please subscribe, share and like our FB page to be updated.