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.

NameDescriptionEE Only?Size (MB)Objects
BI*Business IntelligenceNo08
COCustomer OrdersNo430
HRHuman ResourcesNo234
IXInformation ExchangeYes258
OEOrder EntryNo10106
PMProduct MediaNo722
SCOTT**No0,36
SHSales HistoryNo288295

* 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).

Download of the Oracle Database Sample Schemas

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

3 comments

  1. Hi Christian,

    thank you for the article which is very helpful. I’d also like to know if any of these schemas contain sample PL/SQL Packages or Procedures with sample PL/SQL Code in them.

    thanks
    Ramiro

    1. Hi Ramiro,

      some schemas include PL/SQL code. Here is an overview of the object types.

      BI SYNONYM 8
      HR SEQUENCE 3
      HR VIEW 1
      HR PROCEDURE 2
      HR TRIGGER 2
      SH INDEX 17
      SH INDEX PARTITION 196
      SH DIMENSION 5
      SH VIEW 1
      SH TABLE 12
      IX INDEX 17
      IX EVALUATION CONTEXT 2
      IX TABLE 17
      IX VIEW 8
      IX SEQUENCE 2
      IX QUEUE 4
      IX RULE SET 4
      SH TABLE PARTITION 56
      OE INDEX 36
      OE TYPE 32
      PM TYPE 3
      PM TABLE 2
      PM INDEX 10
      PM LOB 7
      IX TYPE 1
      IX LOB 3
      OE LOB 10
      OE TABLE 12
      OE VIEW 3
      OE TRIGGER 2
      OE FUNCTION 1
      OE TYPE BODY 3
      OE SYNONYM 6
      OE SEQUENCE 1
      HR TABLE 7
      HR INDEX 8

      But it looks like that these PL/SQL objects do not provide any “application logic”.

      Cheers
      Christian

Leave a Reply

Your email address will not be published. Required fields are marked *