Once you have successfully installed Oracle database 19c, its time to unlock the sample schemas so that you can start learning Oracle SQL. In this article, we will see how to unlock HR user in Oracle 19c.

High-level steps to be followed

• Connect to Oracle using SYSDBA

• Verify the pluggable database 

• Switch to pluggable database

• Open the pluggable database (if required) 

• Unlock the HR user

• Connect as HR and verify the schema

Steps:

1. Verify the Pluggable database.

   In Oracle 19c, all sample schemas, including HR schema, are stored inside the pluggable database.

   If you followed our installed Oracle 19c installation. guide, your pluggable database should be ORCLPDB. If you used a different name during installation, you can identify it by querying the V$PDBS view.

   Open the Windows Command Prompt and start SQL*Plus.

                               sqlplus

When prompted, enter your username and password. For example: 

User Name: system Password: oracle

After you connect successfully, you will see the SQL prompt.

(Optional) To make the output easier to read, format the NAME column by using the command below:

                       COLUMN NAME FORMAT A20

The output should look similar to the following:

CON_ID NAME OPEN_MODE

3 ORCLPDB READ WRITE

If your installation uses a different pluggable database name (for example, XEPDB1), substitute that name in the commands used throughout the remainder of this article 

Unlocking HR User in Oracle 19c

Now we can start the process to unlock HR user which will give us the access to HR database.

All the sample databases are stored under the pluggable database (in our installation, it is orclpdb) and not in the root container database. By default, oracle connects us to the root container database. In order for us to unlock the sample database, we need to first switch from the root container to the pluggable database.

• On the cmd, connect using sys user with sysdba privileges. Use the command

                        sqlplus / as sysdba

• Check the container with which we are connected now. Use the command:

                         SHOW CON_NAME;

                   This will show us that we are connected to the root container database CDB$ROOT.

• Use the Alter command to switch from the root container database to the pluggable database.

                        ALTER SESSION SET CONTAINER = ORCLPDB;

• Check if the pluggable database is open. Execute the command:

  SELECT NAME,
  OPEN_MODE
  FROM V$PDBS;

  It will display the open mode.

• We are fine, if it shows Open_mode as READ WRITE.

• If it shows Open_mode as mounted, that means that database is not opened and we have to open it with the command:

                         ALTER PLUGGABLE DATABASE ORCLPDB OPEN;

• Unlock the HR user. To unlock the user, execute the Alter command with the user name, password which you want to use and the action to be performed on this user. Use the following command.

                             ALTER USER HR
                            IDENTIFIED BY hr
                           ACCOUNT UNLOCK;

• If it displays User altered that means our command is successful and HR user is unlocked.

• Test the user. To check if this user is unlocked, use the following command to connect.

                        CONNECT HR/hr@ORCLPDB

• We can also execute the following command to list all the tables:

                           SELECT *
                           FROM TAB;

Chandraish Sinha

Chandraish Sinha is the Founder and President of Ohio Computer Academy, a leading institution committed to delivering high-quality IT education. With a passion for teaching and a belief in his company’s mission—Inspire, Educate & Evolve—Chandraish brings over 25 years of experience in the Information Technology industry.

Explore more of his work on his Amazon Author Profile.