Install Sample Schemas
A quick note as a reminder of how to install the sample schemas into an existing database.
The sample schemas setup scripts are now available from GitHub. Pick the release that matches your database release.
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 184.108.40.206 version of the sample schemas.
cd /tmp unzip /tmp/db-sample-schemas-220.127.116.11.zip cd $ORACLE_HOME/demo/schema cp -R /tmp/db-sample-schemas-18.104.22.168/* . rm -Rf /tmp/db-sample-schemas-22.214.171.124
Replace "__SUB__CWD__" 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.
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 EZ connect string can point to a PDB or a non-CDB service. The number of parameters may change if new sample schemas are added, so remember to check the docs.
Connect to the pluggable database as the SYSTEM user.
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 "syspw" and "systempw" respectively.
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb1
Check the log files for any errors.
The installation scripts drop and create the sample schemas, so to upgrade from a previous version simply run the installation again.
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...