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 23.1 version of the sample schemas, which work in Oracle 19c, 21c and 23c.
cd /tmp unzip /tmp/v23.1.zip cd $ORACLE_HOME/demo/schema rm -Rf $ORACLE_HOME/demo/schema/* cp -R /tmp/db-sample-schemas-23.1/* . rm -Rf /tmp/db-sample-schemas-23.1
The following Perl command will replace "__SUB__CWD__" tag in some of 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 */*.dat
You are now ready to start the installation.
Installation
The following schemas can be installed.
- CO : Customer Orders
- HR : Human Resources
- SH : Sales History
- OE : Order Entry - archived
- PM : Product Media - archived
The installations of each schema vary a little so they are dealt with separately here.
Remember:
- From version 23c of the scripts onward, the non-archived scripts can be run with a DBA user. Prior to this we would use the
SYSTEM
user, which we will use for the remaining examples. - The archived schema creation scripts still need access to SYS. See the examples below.
- In a multitenant environment you must connect to the pluggable database, not the root container.
- The archived scripts are only available for backwards compatibility. The is no new development of them.
Customer Orders (CO)
The schema is installed using the "co_install.sql" script. It prompts for the password, tablespace and if an existing user should be overwritten. So for example the install of the CO
schema would be done like this.
cd $ORACLE_HOME/demo/schema/customer_orders sqlplus system/SysPassword1@//localhost:1521/freepdb1 @co_install.sql
If you prefer to do a silent installation (no prompts) then create the user manually, connect to it and run the relevant scripts. Here is an example of the customer orders installation.
cd $ORACLE_HOME/demo/schema/customer_orders sqlplus system/SysPassword1@//localhost:1521/freepdb1 drop user if exists co cascade; create user co identified by co_password quota unlimited on users; grant connect, resource to co; -- or -- grant db_developer_role to co; conn co/co_password@//localhost:1521/freepdb1 @co_create.sql @co_populate.sql
Human Resources (HR)
The schema is installed using the "hr_install.sql" script. It prompts for the password, tablespace and if an existing user should be overwritten. So for example the install of the CO
schema would be done like this.
cd $ORACLE_HOME/demo/schema/human_resources sqlplus system/SysPassword1@//localhost:1521/freepdb1 @hr_install.sql
Here is an example of a silent human resources installation.
cd $ORACLE_HOME/demo/schema/human_resources sqlplus system/SysPassword1@//localhost:1521/freepdb1 drop user if exists hr cascade; create user hr identified by hr_password quota unlimited on users; grant connect, resource to hr; -- or -- grant db_developer_role to hr; conn hr/hr_password@//localhost:1521/freepdb1 @hr_create.sql @hr_populate.sql @hr_code.sql
Sales History (SH)
The SH sample schema uses the SQLcl LOAD
command, so you will need to use SQLcl to install this schema.
The schema is installed using the "sh_install.sql" script. It prompts for the password, tablespace and if an existing user should be overwritten. So for example the install of the SH
schema would be done like this.
cd $ORACLE_HOME/demo/schema/sales_history sqlplus system/SysPassword1@//localhost:1521/freepdb1 @sh_install.sql
If you prefer to do a silent installation (no prompts) then create the user manually, connect to it and run the relevant scripts. Here is an example of the sales history installation.
cd $ORACLE_HOME/demo/schema/sales_history sqlplus system/SysPassword1@//localhost:1521/freepdb1 drop user if exists sh cascade; create user sh identified by sh_password quota unlimited on users; grant connect, resource, create dimension to sh; -- or -- grant db_developer_role to sh; conn sh/sh_password@//localhost:1521/freepdb1 @sh_create.sql @sh_populate.sql
Order Entry (OE) archived
The order entry installation is already silent. It requires several arguments to be specified, as shown here.
cd $ORACLE_HOME/demo/schema/order_entry sqlplus system/SysPassword1@//localhost:1521/freepdb1 @oe_main.sql oe_password users temp hr_password SysPassword1 $ORACLE_HOME/demo/schema/order_entry/ /tmp/ v3 localhost:1521/freepdb1
The parameters are:
- Password for the OE schema.
- Tablespace
- Temporary tablespace
- Password for the HR schema.
- SYS password.
- Directory path to the files.
- Path for logs to be written to.
- The version "v3".
- The connect script for the database.
Product Media (PM) - archived
The product media installation is already silent. It requires several arguments to be specified, as shown here.
cd $ORACLE_HOME/demo/schema/product_media sqlplus system/SysPassword1@//localhost:1521/freepdb1 @pm_main.sql pm_password users temp oe_password SysPassword1 $ORACLE_HOME/demo/schema/product_media/ /tmp/ $ORACLE_HOME/demo/schema/product_media/ localhost:1521/freepdb1
The parameters are:
- Password for the PM schema.
- Tablespace
- Temporary tablespace
- Password for the OE schema.
- SYS password.
- Directory path to the files.
- Path for logs to be written to.
- Working directory.
- The connect script for the database.
Upgrade
The installation scripts drop and create the sample schemas, so to upgrade from a previous version simply run the installation again.
Remove
Some of the installations have "{schema}_uninstall.sql" scripts, but you could just drop the schemas.
drop user co cascade; drop user hr cascade; drop user oe cascade; drop user pm cascade; drop user sh cascade;
For more information see:
Hope this helps. Regards Tim...