Getting Oracle’s HR Schema

See Oracle: Tips and Tricks for similar articles.

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: download raw file github
  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: run cmd as admin
  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
      Start Oracle Services
  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: new connection
  3. Enter the following: new connection dialog
    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: 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: select regions 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: new connection
    2. Enter “HR” as the Name and “system” for both the Username and Password, and click Connect: new connection dialog
    3. Close the dialog.
  2. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet: 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: select hr connection
  5. Click the second green play button to run the script. This will create the HR user and assign it the appropriate privileges: run-script

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: run cmd as admin
  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: connection properties
  7. Change the username from “system” to “C##HR” and change to password to “HR”: change authentication
  8. Open a blank SQL Worksheet by right-clicking HR under Connections and selecting Open SQL Worksheet: 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: select regions If the query worked, you are all set.

Written by Nat Dunn. Follow Nat on Twitter.


Related Articles

  1. Getting Oracle’s HR Schema (this article)
  2. How to Install Oracle Express Edition and SQL Developer
  3. Oracle’s Demo HR Schema
  4. How to choose between a procedure and a function in PL/SQL