Installation of sample schemas in Oracle 12c and higher

To demonstrate the features of the database, Oracle provides a list of sample schemas, which can be installed in the database. This blog post provides an overview of the existing sample schemas and summarizes the necessary steps to install them.

Overview

The following sample schemas are provided by Oracle. Be aware that some of them are only installable in an Enterprise Edition database.

* The BI schema has no own tables. Instead, it owns 8 synonyms that are pointing to objects of the SH schema.
** Legacy sample schema

Download

The required scripts to install the sample schemas are available in the GitHub repository oracle/db-sample-schemas You can either clone the whole repository or download the scripts as ZIP resp. TAR.GZ file. For the latter one, navigate to the releases area (https://github.com/oracle/db-sample-schemas/releases).

Upload the file to the database server and extract it to a temporary location.

$> unzip -d /tmp db-sample-schemas-19.2.zip

For a successful execution it is required to update the installation scripts using the following Perl call.

$> cd /tmp/db-sample-schemas-19.2
$> perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

Installation

Schema HR

Starting with Oracle 12c, only the HR schema is shipped with the Oracle Database software and installed when you mark the checkbox “Install sample schemas” in the DBCA. To install this schema afterward run the following SQL script.

SQL> @?/demo/schema/human_resources/hr_main.sql

In Oracle 21c the HR schema is not part of the database installation anymore.

If you still want to install the HR schema only, run the following command from the human_resources subdirectory of the downloaded scripts.

SQL> @hr_main.sql

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: USERS

specify temporary tablespace for HR as parameter 3:
Enter value for 3: TEMP

specify password for SYS as parameter 4:
Enter value for 4: manager

specify log path as parameter 5:
Enter value for 5: /tmp/

specify connect string as parameter 6:
Enter value for 6: C21SFE1PDB1

Schemas BI, HR, IX, OE, PM, SH

Now it is time to connect to the database and install the sample schemas. In my example, I am connecting to my Oracle 21c PDB C21SFE1PDB1 using a TNS alias with the same name.

$> sqlplus system/manager@C21SFE1PDB1

The mksample.sql is the root SQL script which can be either run in interactive mode or in batch mode.

SQL> @mksample.sql

specify password for SYSTEM as parameter 1:
Enter value for 1: manager

specify password for SYS as parameter 2:
Enter value for 2: manager

specify password for HR as parameter 3:
Enter value for 3: hr

specify password for OE as parameter 4:
Enter value for 4: oe

specify password for PM as parameter 5:
Enter value for 5: pm

specify password for IX as parameter 6:
Enter value for 6: ix

specify password for  SH as parameter 7:
Enter value for 7: sh

specify password for  BI as parameter 8:
Enter value for 8: bi

specify default tablespace as parameter 9:
Enter value for 9: USERS

specify temporary tablespace as parameter 10:
Enter value for 10: TEMP

specify log file directory (including trailing delimiter) as parameter 11:
Enter value for 11: C21SFE1PDB1

To run mksample.sql in batch mode with the same values as above, use the following call.

SQL> @mksample.sql manager manager hr oe pm ix sh bi USERS TEMP /tmp/ C21SFE1PDB1

Schema CO

Although the CO schema is part of the sample schemas, it is not installed by mksample.sql. Instead, navigate to the customer_orders subdirectory and run the co_main.sql script. Be aware that the order of the parameters is slightly different for this schema.

SQL> co_main.sql
Enter value for 1: co              -- Password of the CO user
Enter value for 2: C21SFE1PDB1     -- Connection string
Enter value for 3: USERS           -- Default tablespace
Enter value for 4: TEMP            -- Default temporary tablespace

Schema SCOTT

If you want to install the legacy and well-known sample schema SCOTT, you have to run the utlsampl.sql script. Despite the creation of the user SCOTT, this script drops the following users.

• ADAMS
• BLAKE
• CLARK
• JONES

SQL> @?/rdbms/admin/utlsampl.sql

Be aware that the utlsampl.sql script does not support the execution within a PDB because it executes a reconnect to the SCOTT schema. You have to change the following line in the script.

Old: CONNECT SCOTT/tiger
New: CONNECT SCOTT/tiger@C21SFE1PDB1 

The SQL script to create SCOTT is also available for download on the GitHub repository oracle/dotnet-db-samples. This script does not include the hardcoded password tiger.

SQL> @scott.sql

References

• Oracle Database 21c Database Sample Schemas