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
The sample schemas setup scripts are now available from GitHub. Pick the release that matches your database release.
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:
Hope this helps. Regards Tim...