Installing Oracle Sample Schemas (19.3)

In this post, I’m going to explain how to install Oracle sample schemas, if they are not already installed.

If you created a database sing DBCA , you can select to install the sample schemas:

Please note that using this method (dbca) will install only the HR sample schema.

Or you can install sample schemas manually after the database creation as explained in this post.

Download the Scripts:

Starting from Oracle database 12.2 ,the sample schemas setup scripts are now available from GitHub. Pick the release that matches your database release:

https://github.com/oracle/db-sample-schemas/releases

Extract the files:

Extract the file “db-sample-schemas-19c.zip” directly into $ORACLE_HOME/demo/schema, replacing any existing files.

Adjust the Working Directory:

Run the following Perl command to replace “__SUB__CWD__” tag in the scripts with your current working directory. If you are using an alternate working directory, change to that directory before running the Perl command.

cd $ORACLE_HOME/demo/schema
perl -p -i.bak -e ‘s#SUB__CWD#’$(pwd)’#g’ *.sql */*.sql */*.dat

Install all sample schemas at once:

The following schemas will be installed.

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

cd /u01/app/oracle/product/12.2.0/dbhome_1/demo/schema
sqlplus / as sysdba
@mksample oracle oracle hr oe pm ix sh bi users temp $ORACLE_HOME/demo/schema/log/ localhost:1521/orcl

where:
oracle: is the password for the system & sys users respectively.
hr is the password for the HR user.
oe is the password for the OE user.
pm is the password for the PM user.
ix is the password for the IXuser.
sh is the password for the SH user.
bi is the password for the HR user.

For Windows:

@mksample oracle oracle hr oe pm ix sh bi users temp D:\app\WINDOWS.X64_193000_db_home\demo\schema\log\ localhost:1529/orcl

[before running the above command, ensure that the “SYSTEM” user is unlocked in both the root and pluggable containers]

You may want to install only individual schemas (e.g. HR or OE), In this case follow the following procedure:

To Install the HR sample schema

In the following example, I’m going to install HR schema into a 19.3 pluggable database “PDB1”.

cd $ORACLE_HOME/demo/schema/human_resources


Then run the following script to create the HR schema:
@hr_main.sql

To Install the OE sample schema:

SQL> @?/demo/schema/order_entry/oe_main.sql

Here is a live demo about OE installation:

https://ahmedfattah.com/2021/03/15/installing-oracle-oe-sample-schema/

How to Drop Sample Schema (e.g. HR)?

Important Note:

If you are in Windows platform, and you don’t have “perl” installed, then you will need to modify the scripts manually to replace “__SUB__CWD__” with “”you working directory that holds the sample schema scripts like :(D:\app\db193\WINDOWS.X64_193000_db_home\demo\schema)”.

If you have cygwin terminal emulator ( or any similar one) installed in your windows machine, then you may use command similar to the following to find the files that contain the “__SUB__CWD__” keyword:

cd /cygdrive/d/app/WINDOWS.X64_193000_db_home/demo/schema/order_entry
find . -name “*.sql”|xargs grep SUB_

To keep it short, the following 21 files need to be updated to change: “__SUB__CWD__” to “D:\app\WINDOWS.X64_193000_db_home\demo\schema”. Also, you need to replace the “/” with the “/” in the file paths:

  • ./bus_intelligence/bi_main.sql
  • ./human_resources/hr_main.sql
  • ./info_exchange/ix_main.sql
  • ./mkplug.sql
  • ./mksample.sql
  • ./mk_dir.sql
  • ./order_entry/coe_v3.sql
  • ./order_entry/coe_xml.sql
  • ./order_entry/createUser.sql
  • ./order_entry/loe_v3.sql
  • ./order_entry/oe_cre.sql
  • ./order_entry/oe_drop.sql
  • ./order_entry/oe_main.sql
  • ./order_entry/poe_v3.sql
  • ./order_entry/xdb03usg.sql
  • ./order_entry/xdbSupport.sql
  • ./product_media/long2lob.sql
  • ./product_media/pm_main.sql
  • ./sales_history/sh_main.sql
  • ./sales_history/sh_olp_c.sql
  • ./shipping/qs_main.sql

If you need an already updated script files for Windows with the “__SUB__CWD__” to “D:\app\WINDOWS.X64_193000_db_home\demo\schema”, then download it from here:
https://drive.google.com/file/d/1q5oqlWe6ioiPFDwgwXYvXhltW7Z_nhvB/view?usp=sharing

Extract the downloaded zip file to: D:\app\WINDOWS.WINDOWS.X64_213000_db_home\demo

In the following video, I’ll show you how can I install all the sample schemas in the “orcl” database (it is 19.3) on Windows. I’ll first remove the currently exist “HR” schema before the installation.
I didn’t edit the video and left all errors faced me to see how I fixed quickly.

What about scott schema!. Ok, this may be the subject of my next post.

Thanks
Ahmed

Advertisement

7 thoughts on “Installing Oracle Sample Schemas (19.3)

    1. Hello Ukiyo,
      Parameter 6 should be the order entry directory where the OE data and control files reside.
      On Windows, it should be “D:\app\db193\WINDOWS.X64_193000_db_home\demo\schema\order_entry”
      Oracle uses the data files (*.dat) and SQL loader control files (*.ctl) to load the OE data.

      I’ll update the post with a short live video on how to install the OE sample schema.

      Thanks
      Ahmed

      Like

    1. Hi Simon
      Parameter 6 should be the order entry directory where the OE data and control files reside.
      On Windows, it should be “D:\app\db193\WINDOWS.X64_193000_db_home\demo\schema\order_entry”
      Oracle uses the data files (*.dat) and SQL loader control files (*.ctl) to load the OE data.

      I’ll update the post with a short live video on how to install the OE sample schema.

      Like

      1. in windows 10 , need to create tablespace / datafile first before installation ?

        I installed with the following error :

        PL/SQL procedure successfully completed.

        DECLARE
        *
        ERROR at line 1:
        ORA-22288: file or LOB operation FILEOPEN failed
        The system cannot find the file specified.
        ORA-06512: at “SYS.DBMS_LOB”, line 822
        ORA-06512: at “XDB.DBMS_XDB”, line 369
        ORA-06512: at “XDB.DBMS_XDB”, line 397
        ORA-06512: at line 4

        BEGIN
        *
        ERROR at line 1:
        ORA-31001: Invalid resource handle or path name “/home/OE/purchaseOrder.xsd”
        ORA-06512: at “SYS.XDBURITYPE”, line 4
        ORA-06512: at line 2

        Call completed.

        Call completed.

        BEGIN
        *
        ERROR at line 1:
        ORA-22288: file or LOB operation FILEOPEN failed
        The system cannot find the file specified.
        ORA-06512: at “SYS.XMLTYPE”, line 296
        ORA-06512: at “XDB.COE_UTILITIES”, line 193
        ORA-06512: at line 2

        Connected.
        revoke execute on directory SUBDIR from OE
        *
        ERROR at line 1:
        ORA-04042: procedure, function, package, or package body does not exist

        Connected.
        Connected.

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s