How to Install Oracle Sample Schemas

Oracle Sample Schemas

Since 12c R2, you can install only HR sample schema during database creation for demo or practice. If you want to use sample schemas other than HR, such as OE, OC, PM, and SH schemas, you must download them from the GitHub repository for Oracle database sample schemas.

In this post, I will show you how to install the complete pack of 19c database sample schemas from the scratch.

1. Download Sample Schemas Installation Pack

For the releases of sample schemas, you can download the installation pack from:

2. Unzip Sample Schemas Installation Pack

[oracle@primary-19c ~]$ unzip -q v19c.zip
[oracle@primary-19c ~]$ cd db-sample-schemas-19c/
[oracle@primary-19c db-sample-schemas-19c]$ ll
total 104
drwxr-xr-x 2 oracle oinstall    85 Apr  6  2018 bus_intelligence
-rw-r--r-- 1 oracle oinstall   117 Apr  6  2018 CONTRIBUTING.md
-rw-r--r-- 1 oracle oinstall  3633 Apr  6  2018 drop_sch.sql
drwxr-xr-x 2 oracle oinstall  4096 Apr  6  2018 human_resources
drwxr-xr-x 2 oracle oinstall    79 Apr  6  2018 info_exchange
-rw-r--r-- 1 oracle oinstall  1050 Apr  6  2018 LICENSE.md
-rw-r--r-- 1 oracle oinstall  2740 Apr  6  2018 mk_dir.sql
-rw-r--r-- 1 oracle oinstall 27756 Apr  6  2018 mkplug.sql
-rw-r--r-- 1 oracle oinstall  7166 Apr  6  2018 mksample.sql
-rw-r--r-- 1 oracle oinstall  6592 Apr  6  2018 mkunplug.sql
-rw-r--r-- 1 oracle oinstall  6123 Apr  6  2018 mkverify.sql
drwxr-xr-x 3 oracle oinstall  4096 Apr  6  2018 order_entry
drwxr-xr-x 2 oracle oinstall  4096 Apr  6  2018 product_media
-rw-r--r-- 1 oracle oinstall  4931 Apr  6  2018 README.md
-rw-r--r-- 1 oracle oinstall  5263 Apr  6  2018 README.txt
drwxr-xr-x 2 oracle oinstall  4096 Apr  6  2018 sales_history
drwxr-xr-x 2 oracle oinstall  4096 Apr  6  2018 shipping

3. Change Embedded Paths into Present Working Directory

Since all embedded paths in the installation pack are represented as a constant __SUB__CWD__, you have to replacing all occurrences of the token __SUB__CWD__ with your current working directory by the following command.

[oracle@primary-19c db-sample-schemas-19c]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

4. Create a Dedicated Tablespace for Sample Schemas

Conventionally, we create a tablespace EXAMPLE to store those sample schemas. But this step is optional, you can use your existing tablespace.

[oracle@primary-19c db-sample-schemas-19c]$ sqlplus / as sysdba
...
SQL> create tablespace example datafile '/u01/app/oracle/oradata/COMPDB/example01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

Now we are ready to install.

5. Install Sample Schemas

This is a complete installation of sample schemas including the following schemas:

• HR: Human Resources
• OE: Order Entry
• PM: Product Media
• IX: Information Exchange
• SH: Sales History
• BI: Business Intelligence

Please execute the following SQL scripts to install all sample schemas. For avoiding interactive mode, we append all parameters behind the SQL script. They are:

1. SYSTEM password
2. SYS password
3. HR password
4. OE password
5. PM password
6. IX password
7. SH password
8. BI password
9. Tablespace for all sample schemas
10. Temp tablespace for all sample schemas
11. Log file directory
12. Connect string

Usually, it's a connect identifier which is an entry in tnsnames.ora. In this case, it's PRIMDB.

SQL> @mksample.sql welcome1 welcome1 hr oe pm ix sh bi example temp $ORACLE_HOME/demo/schema/log primdb

specify password for SYSTEM as parameter 1:

specify password for SYS as parameter 2:

specify password for HR as parameter 3:

specify password for OE as parameter 4:

specify password for PM as parameter 5:

specify password for IX as parameter 6:

specify password for  SH as parameter 7:

specify password for  BI as parameter 8:

specify default tablespace as parameter 9:

specify temporary tablespace as parameter 10:

specify log file directory (including trailing delimiter) as parameter 11:

specify connect string as parameter 12:

Sample Schemas are being created ...

mkdir: cannot create directory ‘/u01/app/oracle/product/19.0.0/dbhome_1/demo/schema/log’: File exists

Connected.

User dropped.

DROP USER oe CASCADE
          *
ERROR at line 1:
ORA-01918: user 'OE' does not exist


DROP USER pm CASCADE
          *
ERROR at line 1:
ORA-01918: user 'PM' does not exist


DROP USER ix CASCADE
          *
ERROR at line 1:
ORA-01918: user 'IX' does not exist


DROP USER sh CASCADE
          *
ERROR at line 1:
ORA-01918: user 'SH' does not exist


DROP USER bi CASCADE
          *
ERROR at line 1:
ORA-01918: user 'BI' does not exist

The complete pack of sample schemas has been installed.