In April 2023, Oracle released an updated Oracle Database Sample Schemas version on GitHub. I was involved in the effort, which not only updated the data but also introduced a new and improved installation mechanism that makes installing the sample schemas much simpler than before. Almost two years later, it’s a good reminder of what has happened and how to install them. So let’s go.
Sample schemas location
You can find the Oracle Database Sample Schemas, the different versions, and ReadMe at https://github.com/oracle-samples/db-sample-schemas. Make sure to spot the small but important icon called “Tags“:
When you click on it, you will be redirected to the Github Releases/Tags page, and when you click on Releases on the top right, you will see all the releases for the schemas:
Downloading the sample schemas
To download the sample schemas, just click on the Source code (zip) or Source code (tar.gz) link of the latest or appropriate release. Alternatively, savvy GitHub users will also know that they can either git clone the repository or even just download main as is. Once downloaded, uncompress the file and enter the new folder:
Installing the sample schemas
One of the things that I’m most proud of about the new sample schemas is that the installation mechanism changed considerably over the older versions:
SYSuser account access is no longer required; any admin user that can create users and grant create privileges for schema resources will suffice- The installation no longer requires Oracle SQL*Loader but can instead be performed with just SQL*Plus or SQLcl (Sales History (
SH) requires SQLcl for its bulkloadcapabilities). - Sample schemas can now be installed independently, while they were always installed together previously
- Only a password for the new schema is required
- An installation verification is printed at the end
For example, to install the famous HR schema, just head into the human_resources folder, connect with an admin user, invoke the hr_install.sql script, and viola:
gvenzl@gvenzl-mac db-sample-schemas-23.3 % ls -al
total 40
drwxrwxr-x@ 12 gvenzl staff 384 Mar 28 2024 .
drwx------@ 421 gvenzl staff 13472 Feb 24 18:49 ..
-rw-rw-r--@ 1 gvenzl staff 16 Mar 28 2024 .gitignore
-rw-rw-r--@ 1 gvenzl staff 1094 Mar 28 2024 LICENSE.txt
-rw-rw-r--@ 1 gvenzl staff 3784 Mar 28 2024 README.md
-rw-rw-r--@ 1 gvenzl staff 3613 Mar 28 2024 README.txt
-rw-rw-r--@ 1 gvenzl staff 1737 Mar 28 2024 SECURITY.md
drwxrwxr-x@ 8 gvenzl staff 256 Mar 28 2024 customer_orders
drwxrwxr-x@ 10 gvenzl staff 320 Feb 24 22:44 human_resources
drwxrwxr-x@ 87 gvenzl staff 2784 Mar 28 2024 order_entry
drwxrwxr-x@ 67 gvenzl staff 2144 Mar 28 2024 product_media
drwxrwxr-x@ 13 gvenzl staff 416 Mar 28 2024 sales_history
gvenzl@gvenzl-mac db-sample-schemas-23.3 % cd human_resources
gvenzl@gvenzl-mac human_resources % sql system@localhost/freepdb1
🔄 Checking SQLcl version...
✅ SQLcl is current.
🚀 Launching SQLcl...
SQLcl: Release 24.4 Production on Mon Feb 24 22:48:21 2025
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Password? (**********?) *********
Last Successful login time: Mon Feb 24 2025 22:48:24 +01:00
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10
SQL 🚀 > @hr_install.sql
Thank you for installing the Oracle Human Resources Sample Schema.
This installation script will automatically exit your database session
at the end of the installation or if any error is encountered.
The entire installation will be logged into the 'hr_install.log' log file.
Enter a password for the user HR: *************
Enter a tablespace for HR [USERS]:
Do you want to overwrite the schema, if it already exists? [YES|no]:
****** Creating REGIONS table ....
Table REGIONS created.
INDEX REG_ID_PK created.
Table REGIONS altered.
****** Creating COUNTRIES table ....
Table COUNTRIES created.
Table COUNTRIES altered.
****** Creating LOCATIONS table ....
Table LOCATIONS created.
INDEX LOC_ID_PK created.
Table LOCATIONS altered.
Sequence LOCATIONS_SEQ created.
****** Creating DEPARTMENTS table ....
Table DEPARTMENTS created.
INDEX DEPT_ID_PK created.
Table DEPARTMENTS altered.
Sequence DEPARTMENTS_SEQ created.
****** Creating JOBS table ....
Table JOBS created.
INDEX JOB_ID_PK created.
Table JOBS altered.
****** Creating EMPLOYEES table ....
Table EMPLOYEES created.
INDEX EMP_EMP_ID_PK created.
Table EMPLOYEES altered.
Table DEPARTMENTS altered.
Sequence EMPLOYEES_SEQ created.
****** Creating JOB_HISTORY table ....
Table JOB_HISTORY created.
INDEX JHIST_EMP_ID_ST_DATE_PK created.
Table JOB_HISTORY altered.
****** Creating EMP_DETAILS_VIEW view ...
View EMP_DETAILS_VIEW created.
****** Creating indexes ...
Index EMP_DEPARTMENT_IX created.
Index EMP_JOB_IX created.
Index EMP_MANAGER_IX created.
Index EMP_NAME_IX created.
Index DEPT_LOCATION_IX created.
Index JHIST_JOB_IX created.
Index JHIST_EMPLOYEE_IX created.
Index JHIST_DEPARTMENT_IX created.
Index LOC_CITY_IX created.
Index LOC_STATE_PROVINCE_IX created.
Index LOC_COUNTRY_IX created.
****** Adding table column comments ...
...
Session altered.
****** Populating REGIONS table ....
PL/SQL procedure successfully completed.
****** Populating COUNTRIES table ....
PL/SQL procedure successfully completed.
****** Populating LOCATIONS table ....
PL/SQL procedure successfully completed.
****** Populating DEPARTMENTS table ....
Table DEPARTMENTS altered.
PL/SQL procedure successfully completed.
****** Populating JOBS table ....
PL/SQL procedure successfully completed.
****** Populating EMPLOYEES table ....
PL/SQL procedure successfully completed.
****** Populating JOB_HISTORY table ....
PL/SQL procedure successfully completed.
Commit complete.
Table DEPARTMENTS altered.
Procedure SECURE_DML compiled
Trigger SECURE_EMPLOYEES compiled
Trigger SECURE_EMPLOYEES altered.
Procedure ADD_JOB_HISTORY compiled
Trigger UPDATE_JOB_HISTORY compiled
Commit complete.
Installation verification
____________________________
Verification:
Table provided actual
______________ ___________ _________
regions 5 5
countries 25 25
departments 27 27
locations 23 23
employees 107 107
jobs 19 19
job_history 10 10
Thank you!
___________________________________________________________
The installation of the sample schema is now finished.
Please check the installation verification output above.
You will now be disconnected from the database.
Thank you for using Oracle Database!
Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.6.0.24.10
gvenzl@gvenzl-mac human_resources %