1. Database Sample Schemas
  2. Installation of the Sample Schemas

2 Installation of the Sample Schemas

Oracle provides the following sample schemas: HR, OE, OC, PM, and SH. You must download the installable zip file from the GitHub repository to use the sample schemas.

This section includes the following topics:

Note:

Starting with Oracle Database 21c, the HR sample schema no longer ships as part of Oracle Database. For more information, see Installing the HR Schema.

2.1 Installing the Sample Schemas

The Sample Schemas can be installed together using a single script.

The procedure to install sample schemas is as follows:
  1. To find the latest version of the sample schemas installation scripts, go to the following GitHub location :
    https://github.com/oracle/db-sample-schemas/releases/latest
    For example, If you want a 19.2 version of the scripts, then go to the following location:
    https://github.com/oracle/db-sample-schemas/releases/tag/v19.2
  2. Clone the GitHub repository, or download the ZIP bundle from GitHub and extract the files.
  3. Use the following syntax from the SQL*Plus command-line interface:
    sqlplus system/systempw@connect_string
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string
    You can also follow the instructions in the README file contained in the zip file.

    The mksample script expects 11 parameters. Provide the password for SYSTEM and SYS, and for schemas HR, OE, PM, IX, and SH. Specify a temporary tablespace and a default tablespace, and make sure that you end the name of the log file directory with a trailing slash.

The mksample script produces the following log files:

  • mkverify.log - This is the Sample Schema creation log file.

  • hr_main.log - This is the HR schema creation log file.

  • oe_oc_main.log - This is the OE schema creation log file.

  • pm_main.log - This is the PM schema creation log file.

  • pm_p_lob.log - This is the SQL*Loader log file for PM.PRINT_MEDIA.

  • ix_main.log - This is the IX schema creation log file.

  • sh_main.log - This is the SH schema creation log file.

  • cust.log - This is the SQL*Loader log file for SH.CUSTOMERS.

  • prod.log - This is the SQL*Loader log file for SH.PRODUCTS.

  • promo.log - This is the SQL*Loader log file for SH.PROMOTIONS.

  • sales.log - This is the SQL*Loader log file for SH.SALES.

  • sales_ext.log - This is the external table log file for SH.COSTS.

Note:

  • Only HR and SH schemas can be installed independently. The rest of the schemas have dependencies and must be installed together using the @mksample script. See Installing the HR Schema.
  • The master script @mksample currently does not include the CO schema. You must install it separately. See Installing the CO schema.
  • By installing any of the Oracle Database sample schemas, you drop any previously installed schemas that use the following user names: HR, OE, PM, SH, IX, BI, CO.
  • Data contained in any of these schemas is lost if you run any of the installation scripts described in this section. You should not use the sample schemas for your personal or business data and applications. They are meant to be used for demonstration purposes only.

2.2 Installing the HR Schema

You can install the HR schema independently. All scripts necessary to create the Human Resource (HR) schema reside in the human_resources folder of the sample schema installation scripts downloaded earlier. You need to call only one script, hr_main.sql, to create all the objects and load the data.

Perform the following steps to install the HR schema:

  1. Download the sample schema installation zip from GitHub and extract the files.
  2. Navigate to the human_resources folder.
  3. Log on to SQL*Plus as SYS and connect using the AS SYSDBA privilege:
    sqlplus connect sys as sysdba
Enter password: password
  4. To run the hr_main.sql script, use the following command:
    SQL> @hr_main.sql
  5. Enter a secure password for HR:
    specify password for HR as parameter 1:
Enter value for 1:
  6. Enter an appropriate tablespace, for example, users as the default tablespace for HR:
    specify default tablespace for HR as parameter 2:
Enter value for 2:
  7. Enter temp as the temporary tablespace for HR:
    specify temporary tablespace for HR as parameter 3:
Enter value for 3:
  8. Enter the password for SYS:
    specify password for SYS as parameter 4:
Enter value for 4:
  9. Enter the directory path, for example, $ORACLE_HOME/demo/schema/log/, for your log directory:
    specify log path as parameter 5:
Enter value for 5:

After the hr_main.sql script runs successfully and the HR schema is installed, you are connected as user HR. To verify that the schema was created, use the following command: 

SQL> SELECT table_name FROM user_tables;

Running hr_main.sql accomplishes the following tasks:

  1. Removes any previously installed HR schema.
  2. Creates user HR and grants the necessary privileges.
  3. Connects as HR.
  4. Calls the scripts that create and populate the schema objects.

For a complete listing of the scripts and their functions, refer to HR Sample Schema Scripts and Objects.

A pair of optional scripts, hr_dn_c.sql and hr_dn_d.sql, is provided as a schema extension. To prepare schema HR for use with the directory capabilities of Oracle Internet Directory, run the hr_dn_c.sql script. If you want to return to the initial setup of schema HR, use the hr_dn_d.sql script to undo the effects of the hr_dn_c.sql script.

To drop the HR schema, run the following script:
SQL>@hr_drop.sql

Note:

Similarly SH schema can be installed independently.

See Also:

Oracle Database Security Guide for the minimum password requirements

2.3 Installing the CO schema

You can install the CO schema independently from GitHub. You cannot install the CO schema as part of the @mksample script.

The steps to install CO schema from GitHub is as follows:

  1. Go to the following GitHub location:
  2. Clone the GitHub repository, or download the ZIP bundle from GitHub and extract the files.
  3. Navigate to the customer_orders folder.
  4. Follow the instructions in the README.txt present in the customer_orders folder.
  5. Review the co_install.log file in the extracted zip folder for errors.
  6. To verify that the schema was created, use the following command:
    SQL> SELECT table_name FROM user_tables;
  7. To drop the CO schema, run the following script:
    SQL> @co_drop_user.sql

Note:

  • The Customer Orders(CO) schema is available from Oracle Database 12c onwards.
  • The master script @mksample currently does not include the CO schema.

2.4 Resetting Sample Schemas

Typically, there is no difference between installing a Sample Schema for the first time or reinstalling it over a previously installed version. The *_main.sql scripts drop the schema users and all of their objects from the previous installation.

Therefore, to reset the Sample Schemas, follow the steps mentioned in the section Installing the Sample Schemas.

2.5 Uninstalling Sample Schemas

To uninstall the sample schemas, run the drop_sch.sql script on the SQL*Plus command line. This script ships with Oracle Database.

This script uses the following parameters:

  • systempwd

  • SYSTEM

  • connect_string

The systempwd is the password for SYSTEM user and connect_string is the connection string of the database.

Example 2-1 How to Uninstall Sample Schemas

sqlplus system/systempw@connect_string
@drop_sch.sql