In this post, I’m going to explain how to install Oracle database sample schemas on Oracle database version 21c.
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-21.1.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 $ORACLE_HOME/demo/schema
sqlplus sys/oracle@localhost:1531/pdb1 as sysdba
@mksample oracle oracle hr oe pm ix sh bi users temp $ORACLE_HOME/demo/schema/log/ localhost:1531/pdb1
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_213000_db_home\demo\schema\log\ localhost:1531/pdb1
[before running the above command, ensure that the “SYSTEM” user is unlocked in both the root and pluggable containers]
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 your 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 a command similar to the following to find the files that contain the “__SUB__CWD__” keyword:
cd /cygdrive/d/app/WINDOWS.X64_213000_db_home/demo/schema/
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.WINDOWS.X64_213000_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.WINDOWS.X64_213000_db_home\demo\schema”, then download it from here:
https://drive.google.com/file/d/1lYNydFvDv4esCETGe_ZaYDt_Xpwnd399/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 “pdb1” database (it is 21c database) on Windows:
Ahmed AbdelFattah Blog ! 