DBA Hub

📋Steps in this guide1/10

Install Sample Schemas

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

oracle miscconfigurationintermediate
by OracleDba
25 views
1

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
2

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 23ai. 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. You are now ready to start the installation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
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

cd $ORACLE_HOME/demo/schema
perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' */*.sql */*.dat
3

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 23ai of the scripts onward, the non-archived scripts can be run with a DBA user. Prior to this we would use the 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.
4

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 schema would be done like this. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
cd $ORACLE_HOME/demo/schema/customer_orders

sqlplus system/SysPassword1@//localhost:1521/freepdb1

@co_install.sql

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
5

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 schema would be done like this. Here is an example of a silent human resources installation.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
cd $ORACLE_HOME/demo/schema/human_resources

sqlplus system/SysPassword1@//localhost:1521/freepdb1

@hr_install.sql

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
6

Sales History (SH)

The SH sample schema uses the SQLcl 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 schema would be done like this. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
cd $ORACLE_HOME/demo/schema/sales_history

sqlplus system/SysPassword1@//localhost:1521/freepdb1

@sh_install.sql

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
7

Order Entry (OE) archived

The order entry installation is already silent. It requires several arguments to be specified, as shown here. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
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
8

Product Media (PM) - archived

The product media installation is already silent. It requires several arguments to be specified, as shown here. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
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
9

Upgrade

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

Remove

Some of the installations have "{schema}_uninstall.sql" scripts, but you could just drop the schemas. For more information see: - Oracle Database Sample Schemas : Releases Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
drop user co cascade;
drop user hr cascade;
drop user oe cascade;
drop user pm cascade;
drop user sh cascade;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!