Getting Oracle’s HR Schema

In this article, I’ll walk you through installing Oracle’s demo HR schema, which is used in Oracle tutorials, documentation, and in Webucator’s Oracle courses. I’ll also show you how to connect to the schema with SQL Developer.

Instructions

1. Go to https://github.com/connormcd/misc-scripts/blob/master/hr_quick_start.sql
2. Right-click the Raw link:
3. Save the file to an easy location to remember (e.g., the root of your C drive.
4. Run the Command Prompt as administrator:
5. Run the following commands:
   1. Change directory to the folder in which you save hr_quick_start.sql (e.g., \):

      cd \

   2. Log into SQL*Plus as system using your system password:

      sqlplus system/password@//localhost/XEPDB1

      You should get a SQL prompt:

      SQL> 

      If you get either of the following errors, open Services and start (or restart) both of the following services:
      1. OracleOraDB21Home1TNSListener
      2. OracleServiceXE
      
6. Create and populate the HR Schema by running the hr_quick_start.sql script:

   SQL> @hr_quick_start.sql

   Follow the instructions, pressing Enter when prompted. IMPORTANT: Be sure to note your password when prompted. You should see a lot of messages. The final message should be:

   **** INSTALLATION COMPLETE ****

7. To confirm the installation actually did complete, run the following query:

   SQL> SELECT * FROM Regions;

   You should get the following results:

    REGION_ID REGION_NAME
   ---------- -------------------------
            1 Europe
            2 Americas
            3 Asia
            4 Middle East and Africa
   
   4 rows selected.

Connect to the HR Schema from SQL Developer

1. Open SQL Developer.
2. Click the + icon under Connections:
3. Enter the following:
   1. Name: HR
   2. Username: HR
   3. Password: Enter the password you noted when running hr_quick_start.sql.
   4. Hostname: localhost
   5. Port: 1521
   6. Service Name: XEPDB1
   Click Test. You should see a Success message in the bottom left of the dialog.
4. Click Connect.
5. Close the dialog.
6. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet:
7. Enter the following SQL query in the new worksheet:

   SELECT * FROM Regions;

8. Run the query by clicking on the left green triangle. You should get 4 rows in the Query Result: If the query worked, you are all set.

Installing the HR Schema in the CDB

There are two pieces involved:

1. Setting up the HR Account.
2. Getting the HR Schema.

For an overview of the HR schema, see Oracle’s Demo HR Schema.

Set Up the HR Account

1. In SQL Developer, add a new connection:
   1. Click the + icon under Connections:
   2. Enter “HR” as the Name and “system” for both the Username and Password, and click Connect:
   3. Close the dialog.
2. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet:
3. Enter the following code:

   DROP USER C##HR CASCADE;
   CREATE USER C##HR IDENTIFIED BY HR
   DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE 
   TEMP ACCOUNT UNLOCK;
   
   ALTER USER C##HR ACCOUNT UNLOCK;
   ALTER USER C##HR QUOTA UNLIMITED ON USERS;
   
   GRANT CONNECT, RESOURCE, CREATE VIEW TO C##HR;

4. Select HR from the connection dropdown in the upper right:
5. Click the second green play button to run the script. This will create the HR user and assign it the appropriate privileges:

Get the HR Schema

1. Go to https://github.com/oracle/db-sample-schemas/releases/latest and download the Source code (zip) to get the sample schemas.
2. Unzip the sample schemas to the root of you C drive). That will create a C:\db-sample-schemas-21.1 folder.
3. Run the Command Prompt as administrator:
4. Run the following commands:
   1. Change directory to the human_resources folder within the db-sample-schemas folder:

      cd \db-sample-schemas-21.1\human_resources

   2. Start SQL*Plus as the HR user:

      sqlplus C##HR/HR

5. Create and populate the HR Schema by running the following two commands:

   SQL> @hr_cre.sql

   SQL> @hr_popul.sql

   You should see a lot of messages. The final message should be:

   Commit complete.

6. Open SQL Developer and open the properties of the HR connection by right-clicking it and selecting Properties:
7. Change the username from “system” to “C##HR” and change to password to “HR”:
8. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet:
9. Enter the following SQL query in the new worksheet:

   SQL> SELECT * FROM Regions;

10. Run the query by clicking on the left green triangle. You should get 4 rows in the Query Result: If the query worked, you are all set.