8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Install Sample Schemas

A quick note as a reminder of how to install the sample schemas into an existing database.

Download
Prepare
Installation
Upgrade
Remove

Download

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

Prepare

The scripts use a token to represent the working directory, so you can install them from anywhere, but I prefer to place them in the "$ORACLE_HOME/demo/schema" directory. Unzip the files into the "$ORACLE_HOME/demo/schema" directory. In this example we used the 12.2.0.1 version of the sample schemas.

cd /tmp
unzip /tmp/db-sample-schemas-12.2.0.1.zip
cd $ORACLE_HOME/demo/schema
cp -R /tmp/db-sample-schemas-12.2.0.1/* .
rm -Rf /tmp/db-sample-schemas-12.2.0.1

The following Perl command will 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

You are now ready to start the installation.

Installation

The following schemas will be installed.

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

The installation scripts drop the existing schemas before installation, so don't continue if you have an existing schema name that matches any of these sample schema names.

The schemas are installed using the mksample.sql file, which requires the following parameters.

@?/demo/schema/mksample system_password sys_password
 		        hr_password oe_password pm_password ix_password 
		        sh_password bi_password tablespace_name temp_tablespace_name
		        log_location ez_connect_string

The new tablespace will be created, as will the log directory if it is not already present. The number of parameters may change if new sample schemas are added, so remember to check the docs.

Remember:

In a multitenant environment you must connect to the SYSTEM@PDB to run the command, and the EZ connect URL must be to the PDB, not the CDB root container.
In a non-CDB environment you are always just connecting to the instance.

Connect to the pluggable database as the SYSTEM user.

sqlplus system/SystemPassword1@pdb1

Load the sample schemas. For simplicity we'll keep the passwords similar to the username. The following command assumes the SYS and SYSTEM passwords are "SysPassword1" and "SystemPassword1" respectively.

@mksample SystemPassword1 SysPassword1 hrPassword1 oePassword1 pmPassword1 ixPassword1 shPassword1 biPassword1 users temp $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb1

Check the log files for any errors.

Upgrade

The installation scripts drop and create the sample schemas, so to upgrade from a previous version simply run the installation again.

Remove

You could just drop the schemas using the cascade option and the tablespace that was created.

DROP USER hr CASCADE;
DROP USER oe CASCADE;
DROP USER pm CASCADE;
DROP USER ix CASCADE;
DROP USER sh CASCADE;
DROP USER bi CASCADE;
DROP TABLESPACE example INCLUDING CONTENTS AND DATAFILES;

Alternatively log on as the SYSTEM user and run the "drop_sch.sql" script.

sqlplus system/systempw@pdb1

@drop_sch.sql

For more information see:

Oracle Database Sample Schemas : Releases

Hope this helps. Regards Tim...

Back to the Top.