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

Home » Articles » Misc » Here

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.

The installations of each schema vary a little so they are dealt with separately here.

Remember:

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:

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:

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...

Back to the Top.