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 Smile

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 grab the scripts here

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