If you head over to the official database sample schema github repo you’ll find all the standard schemas that you’ll typically see features demonstrations on. There are seven schemas in total, namely
- HR
- OE
- PM
- IX
- SH
- BI
- CO
If you’re are dinosaur like myself you’ll recall that the sample schemas used to be distributed along with the database software itself. Changing this to a standalone package on github gives us a lot more flexibility in terms of releasing updates etc, but it also presents a dilemma when it comes to “honoring” it’s roots. For long term customers who have built solutions assuming the layout, folder structure, installation process of the old style distribution, we’ve kept that layout the same here on github. Conversely, that means an assumption of greater knowledge of the process, because it is a more complicated set of steps for new customers to install the scripts because:
- they need SYSDBA access, which can make cloud installations problematic,
- they need to edit the scripts before installation, which can make automation more problematic,
- it is an all nothing process (you get all schemas or no schemas), which might seem a non-issue, but in my experience, there are three schema’s that are used far more frequently than the others – namely HR (Human Resource), SH (Sales History) and CO (Customers Order), so tinkering with the scripts to ensure that all schemas can be correctly installed just so that the HR, SH and CO schemas will work can be frustrating for those new to the Oracle Database.
We’ve heard this feedback and rest assured, there is a project underway within Oracle to revamp the sample schema offering so that they are much easier to install and use, with more flexibility and use cases to be incorporated. These things do take time so in the interim, here’s something that you might find useful. For the three most common schemas (HR, CO and SH) I’ve consolidated all of the various child scripts into a single installation script. This makes the whole process easier, because there is now just a single “run and done” script for each of the HR, CO and SH schemas. There are no intermediate scripts hence no path issues, no sqlldr, no external dependencies whatsoever – you just run them in SQLcl or SQLPlus and you’re good to go.
But also, given that sample schemas are often desired by novice users of the Oracle database, the scripts also do their best to guide the novice, as per the README:
REM Things the scripts will do its best to help you:
REM
REM – not let you install into a root container by mistake
REM – make sure you are connected correctly
REM – if you are connected as HR, we’ll try reload the schema objects and keep the user
REM – if you are connected not as HR, we’ll try drop/recreate the HR schema
REM – we check for required privs in the HR schema if needed
REM – we check for required privs for an admin account to build the HR schema from scratch
REM – we check for appropriate tablespace quotas
REM – we check for default tablespaces
REM – we check for existing sessions as HR which would block a drop of the user
REM – we check for OS file writability for spool files
In each case, the script will try to provide guidance on remedial action to follow if the installation fails or cannot proceed. Given the intricacies of roles, system privileges etc there is no cast iron guarantee that the various checks will ensure a successful installation but it will at least give you a fighting chance 
Here’s an example of the output from loading the HR schema
| 1) Preliminary checks
| =====================
|
| You should be connected to the database at this point.
| If you are, then you will see the following:
|
| >>> Connected as: YOUR_USER <<<
|
| If you are not, you're will see the following
|
| >>>> SP2-0640: Not connected <<<<
|
| If you get this error, press Ctrl-C to exit this script and
| connect first before running it again.
|
| Tip: For Express Edition, the command to connect is *probably*
|
| SQL> connect system/yourpassword@//localhost/XEPDB1
|
| Once you are connected OK, then press Enter to proceed
|
Connected as: MCDONAC
Enter to proceed, Ctrl-C to stop
|
| Checking that we can write a file to the current directory
| If we can't, then this script will exit here. Please make
| you are running the script from the directory you saved it to
| and this directory is writable
|
File test passed!
|
|
| Now checking database details. If any of these fail,
| the script will exit with the error that you need
| to resolve.
|
Container database. PDB PDB1 will be used for installation...proceeding
|
| Checking current user details
|
You are connected as MCDONAC, ie, not the HR schema.
Hence this installation will drop the HR schema entirely
and recreate it. If this was not what you wanted, then press Ctrl-C
the installation, otherwise press Enter to continue
Enter to proceed, Ctrl-C to stop
|
| Checking required privileges
|
Privilege ALTER ANY TABLE..................OK
Privilege ALTER ANY TRIGGER................OK
Privilege ALTER SESSION....................OK
Privilege ALTER USER.......................OK
Privilege ANALYZE ANY......................OK
Privilege COMMENT ANY TABLE................OK
Privilege CREATE ANY CLUSTER...............OK
Privilege CREATE ANY INDEX.................OK
Privilege CREATE ANY INDEXTYPE.............OK
Privilege CREATE ANY OPERATOR..............OK
Privilege CREATE ANY PROCEDURE.............OK
Privilege CREATE ANY SEQUENCE..............OK
Privilege CREATE ANY SYNONYM...............OK
Privilege CREATE ANY TABLE.................OK
Privilege CREATE ANY TRIGGER...............OK
Privilege CREATE ANY TYPE..................OK
Privilege CREATE ANY VIEW..................OK
Privilege CREATE SESSION...................OK
Privilege CREATE USER......................OK
Privilege DELETE ANY TABLE.................OK
Privilege DROP USER........................OK
Privilege GRANT ANY OBJECT PRIVILEGE.......OK
Privilege GRANT ANY PRIVILEGE..............OK
Privilege INSERT ANY TABLE.................OK
Privilege SELECT ANY TABLE.................OK
Privilege UPDATE ANY TABLE.................OK
Privilege SELECT ON GV$SESSION.............OK
|
| Checking tablespaces
|
Checks .......OK
|
| Checking existing HR details
|
Checks .......OK
|
| The new/replaced HR schema will be created now.
|
| Note down this password for the HR schema. You will need it to connect
|
| Password (case-sensitive): DAjcBlFOJq$986
|
| The script will exit on any error encountered, because it should run
| to completion with no errors at all
|
Press Enter to start
****** Creating REGIONS table ....
Table created.
Index created.
Table altered.
****** Creating COUNTRIES table ....
Table created.
Table altered.
****** Creating LOCATIONS table ....
Table created.
Index created.
Table altered.
Sequence created.
****** Creating DEPARTMENTS table ....
Table created.
Index created.
Table altered.
Sequence created.
****** Creating JOBS table ....
Table created.
Index created.
Table altered.
****** Creating EMPLOYEES table ....
Table created.
Index created.
Table altered.
Table altered.
Sequence created.
****** Creating JOB_HISTORY table ....
Table created.
Index created.
Table altered.
****** Creating EMP_DETAILS_VIEW view ...
View created.
Commit complete.
Session altered.
****** Populating REGIONS table ....
1 row created.
1 row created.
1 row created.
1 row created.
****** Populating COUNTIRES table ....
1 row created.
1 row created.
1 row created.
1 row created.
[snip]
Commit complete.
PL/SQL procedure successfully completed.
OBJECT_NAME OBJECT_TYPE
---------------------------------------- ----------------------------------------
COUNTRY_C_ID_PK INDEX
DEPT_ID_PK INDEX
DEPT_LOCATION_IX INDEX
EMP_DEPARTMENT_IX INDEX
EMP_EMAIL_UK INDEX
EMP_EMP_ID_PK INDEX
EMP_JOB_IX INDEX
EMP_MANAGER_IX INDEX
EMP_NAME_IX INDEX
JHIST_DEPARTMENT_IX INDEX
JHIST_EMPLOYEE_IX INDEX
JHIST_EMP_ID_ST_DATE_PK INDEX
JHIST_JOB_IX INDEX
JOB_ID_PK INDEX
LOC_CITY_IX INDEX
LOC_COUNTRY_IX INDEX
LOC_ID_PK INDEX
LOC_STATE_PROVINCE_IX INDEX
REG_ID_PK INDEX
ADD_JOB_HISTORY PROCEDURE
SECURE_DML PROCEDURE
DEPARTMENTS_SEQ SEQUENCE
EMPLOYEES_SEQ SEQUENCE
LOCATIONS_SEQ SEQUENCE
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE
SECURE_EMPLOYEES TRIGGER
UPDATE_JOB_HISTORY TRIGGER
EMP_DETAILS_VIEW VIEW
34 rows selected.
**** INSTALLATION COMPLETE ****
SQL>
You can view the content of the scripts here
- HR https://github.com/connormcd/misc-scripts/blob/master/hr_quick_start.sql
- CO https://github.com/connormcd/misc-scripts/blob/master/co_quick_start.sql
- SH https://github.com/connormcd/misc-scripts/blob/master/sh_quick_start.sql
but since SQL*Plus allows you run a script directly via http, you could also just run them directly via the links below
- https://raw.githubusercontent.com/connormcd/misc-scripts/master/hr_quick_start.sql
- https://raw.githubusercontent.com/connormcd/misc-scripts/master/co_quick_start.sql
- https://raw.githubusercontent.com/connormcd/misc-scripts/master/sh_quick_start.sql
Note: In order to allow the SH schema to load in a single script with no dependencies, there’s some very large INSERT PL/SQL blocks in it, so its going to take a while the run.
As always, these are “use at your own risk” because they are just some idle tinkerings of mine to make the sample schema process easier for everyone until we revamp them officially at a later date.
Photo by Amanda Jones on Unsplash
Learning is not a spectator sport 
Got some thoughts? Leave a comment