If you have failed to unlock & connect with the sample schemas multiple times & wondering if Oracle has discontinued shipping them with the software, don’t worry they are right there under your nose. It’s just that you were searching them at the wrong place. Let me help you find them.
In this tutorial we will learn how to unlock a sample user Like HR in Oracle Database 18c. For the demonstration I will unlock the HR schema but you can follow these same steps to unlock any other sample schema that you want.
In this video we will be using the same settings that we did in the previous video while installing 18c. So I would suggest you to go ahead and watch the installation video first.
Unlocking a sample schema in Oracle Database 18c is a five step process. These are:
- Finding out the container ID.
- Finding out the service name.
- Creating an entry in “TNSNAMES.ora” file.
- Switching from Container database to Pluggable database.
- Unlocking the HR user
Let’s see each of these steps in detail.
Step 1: Find out the Container ID
In order to unlock the user we need to find out the database service corresponding to our pluggable database which contains our sample schemas. For this we require the container ID of that pluggable database. To find out the container ID you first need to connect to your database using sys user.
Sqlplus / as sysdba
Once you are successfully connected to your database using sys user then you can query the v$PDBS dynamic performance view to see the names and the container IDs of all the pluggable databases created inside the container database with which you are currently connected. Like this
SELECT name, con_id FROM v$pdbs;
The output of this SQL query will show us the names and the container ids of all the pluggable databases created inside the CBD$ROOT container database.
Which pluggable database has all our sample schema?
The result of the above query will give us the list of all the pluggable databases created inside the root container with which you are currently connected. From that list you will need to note down the name and container ID of the default pluggable database. Default pluggable database is the one which we created during the installation. Usually the name of that default pluggable database in Oracle Database 18c is ORCLPDB.
What is this PDB$SEED?
PDB$SEED is the seed pluggable database & a system supplied template which a container database uses for creating user defined pluggable databases. I suggest you not to tamper with this.
For this demonstration the name of the default pluggable database where sample schemas are located is ORCLPDB and its corresponding con_id is 3.
STEP 2: Find out the service name.
Now using the container ID of ORCLPDB pluggable database we will find out its service name. Usually, pluggable database and its service shares the same name, but its good practice to check it beforehand. To find out the service name corresponding to our pluggable database we can query v$active_services dynamic performance view.
name as “Service Name”
WHERE con_id =3;
Using this query we are retrieving the service name corresponding to container ID 3 which is the container ID for the default pluggable database ORCLPDB which has all our sample schemas.
The service name for the pluggable database ORCLPDB is orclpdb.
Step 3: Create an entry in TNSNAMES.ora file.
Using the name of our pluggable database and its corresponding service we need to create an entry for our listener. There are two ways of doing that, we can either use NETCA utility or we can do it manually. I found that manually creating an entry in TNSNAMES.ora file is less time consuming.
To create an entry for listener in TNSNAMES.ora file you first need to locate it. Tnsnames.ora is a network configuration file thus you will find it inside the “Network” folder which in turn is located inside our DB_HOME.
TNSNAMES.ora file location
Once you’ve located your tnsnames.ora file then create an entry at the end of the file and save it. The Entry will look something like this.
For more details on how to create a tnsnames.ora entry please watch my YouTube Video, here. I have also uploaded my tnsnames.ora file on the GitHub, which you can download from here.
Restart the listener.
To bring the new changes that we just made into the tnsnames.ora file into action we need to restart the listener. You can do so just by writing this below given command into your command prompt. Just make sure to start your command prompt with administrative privileges
Step 4: Switch from Container database to Pluggable database.
So far, one thing which is very clear to all of us is that all the high privileged users like sys are placed inside a container database and all the sample schemas are placed inside the default pluggable database, which means in order to unlock them we need to switch from the container database to the pluggable database.
To switch from the container database CDB$ROOT to the desirable pluggable database ORCLPDB we can use ALTER SESSION DDL. Like this
ALTER SESSION SET container = ORCLPDB;
Step 5: Unlock the HR user
Again for this demonstration I am unlocking the HR user, whereas you can use these steps for unlocking any other sample user that you want.
Check if the pluggable database is opened?
In order to perform any DDL or DML the database needs to be in open mode. Chances are that the pluggable database over which you just switched may not be opened or in mount state. To check the open mode of a pluggable database you can write a query on V$PDBS dynamic performance view, like this
SELECT name, open_mode FROM v$pdbs;
The output of the above SELECT statement will show you the open mode along with the name of the database which you are currently connected to.
The open mode of my pluggable database is showing “MOUNTED”?
If the open mode of your pluggable database is showing mounted then that means your pluggable database isn’t open. In order to proceed ahead and unlock the sample user you need to open your database. To open your pluggable database you can write a very simple ALTER DDL like this
ALTER PLUGGABLE DATABASE open;
On successful execution this DDL statement will open your pluggable database.
Unlock the Sample HR user.
Once you’ve done all the above steps and configured your database properly then you are all set to unlock any sample user you want. For example let’s say we want to unlock the HR user. The query for that will be
ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;
On successful execution this DDL will unlock your HR user in Oracle Database 18c.
That’s how we unlock sample user in Oracle Database 18c. Hope you enjoyed. If you have any doubts then feel free to shoot me a message on my Facebook. Thanks & have a great day!