Oracle Sample Schemas

Well, I believe most of DBAs including myself do not want to create Oracle sample schemas when creating a database. Why bother because we thought we would never use those sample schemas anyway. However, recently I’m running into an issue when trying to use tutorials on Oracle Data Mining (ODM). It requires the Sale History (SH) schema. Since database is already created, when I run “dbca” again on that database and select “Configure Database Options”, the “Sample Schemas” option is grayed out.

In order to install these sample schemas manually on the existing Oracle 10g home, a script called “mksample.sql” located at @?/demo/schema is needed. By default, the Enterprise Edition version does not include this script when binaries are installed.

D:\oracle\product\10.2.0\db_1\demo\schema> ls
bus_intelligence
mk_dir.sql.ouibak sales_history

human_resources
mk_dir.sql.sbs
sted_mkplug.sql.dbl

log mkplug.sql
mk_dir.sql
order_entry

You will need to download the 10g Companion CD, run “Setup.exe”, select to install the “Oracle Database 10g Productions” (2nd option) then specify the existing Oracle Home. The installation takes about 10 minutes. After the installation, now mksample.sql is there.

D:\oracle\product\10.2.0\db_1\demo\schema>ls
README.txt
mk_dir.sql.ouibak
mkverify.sql

bus_intelligence
mk_dir.sql.ouibak.1
order_entry

human_resources
mkplug.sql
product_media

info_exchange
mksample.sql

sales_historylog

mksample.sql.sbs
shippingmk_dir.sql
mkunplug.sql

sted_mkplug.sql.dbl

To install the sample schemas, just simply run the following at the SQL prompt –

> sqlplus /nolog
SQL>@mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temporary_tablespace log_file_directory

Example:
@mksample manager change_on_install hr oe pm ix sh bi example temp $ORACLE_HOME/demo/schema/log/

The process takes about 20 minutes.

Please note that this same script can be used if there is a need to reset data of these sample schemas.

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top