2 Installing Sample Schemas
Starting with Oracle Database 12c Release 2, the latest version of the sample schema scripts are available on GitHub at https://github.com/oracle/db-sample-schemas/releases/latest.
During a complete installation of Oracle Database, the HR schema can be installed either manually or automatically when creating a database using the
dbca option. All the other sample schemas must be installed manually via the scripts available on GitHub.
This chapter contains the following topics:
By installing any of the Oracle Database sample schemas, you will drop any previously installed schemas that use the following user names:
Data contained in any of these schemas will be lost if you run any of the installation scripts described in this section. You should not use the sample schemas for your personal or business data and applications. They are meant to be used for demonstration purposes only.
2.1 Installing HR Schema Only
This section contains the following topics:
2.1.1 Installing HR Schema Using Database Configuration Assistant
sample schemas option to install
HR schema in the database.
At the end of the installation process, a dialog box displays the accounts that have been created and their lock status. By default, sample schemas are locked and their passwords are expired. Before you can use a locked account, you must unlock it and reset its password. You can unlock the accounts at this point in the installation process. Alternatively, after the installation completes, you can unlock the schemas and reset their passwords by using the
ALTER USER ... ACCOUNT UNLOCK statement. For example:
ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY
"Guidelines for Securing Passwords" in Oracle Database Security Guide for guidelines related to creating secure passwords
2.1.2 Manually Installing the HR Schema
All scripts necessary to create the Human Resource (
HR) schema reside in
You need to call only one script,
hr_main.sql, to create all the objects and load the data. The following steps provide a summary of the installation process:
Log on to SQL*Plus as
sqlplus connect sys as sysdba Enter password:
To run the
hr_main.sqlscript, use the following command:
Enter a secure password for
specify password for HR as parameter 1: Enter value for 1:
Enter an appropriate tablespace, for example,
usersas the default tablespace for
specify default tablespace for HR as parameter 2: Enter value for 2:
tempas the temporary tablespace for
specify temporary tablespace for HR as parameter 3: Enter value for 3:
specify password for SYS as parameter 4: Enter value for 4:
Enter the directory path, for example,
/demo/schema/log/, for your log directory
specify log path as parameter 5: Enter value for 5:
hr_main.sql runs successfully and schema
HR is installed, you are connected as user
HR. To verify that the schema was created, use the following command:
SQL> SELECT table_name FROM user_tables;
hr_main.sql accomplishes the following tasks:
- Removes any previously installed
- Creates user
HRand grants the necessary privileges
- Connects as
- Calls the scripts that create and populate the schema objects
For a complete listing of the scripts and their functions, refer to HR Sample Schema Scripts and Objects.
A pair of optional scripts,
hr_dn_d.sql, is provided as a schema extension. To prepare schema
HR for use with the directory capabilities of Oracle Internet Directory, run the
hr_dn_c.sql script. If you want to return to the initial setup of schema
HR, use script
hr_dn_d.sql to undo the effects of script
You can use script
hr_drop.sql to drop schema
Oracle Database Security Guide for the minimum password requirements
2.2 Installing Sample Schemas from GitHub
Starting with Oracle Database 12c Release 2, only the
HR sample schema SQL scripts are available in the
$ORACLE_HOME/demo/schema/human_resources directory. If you want to use sample schemas other than
HR, such as
OE, OC, PM, and
SH schemas, you must download them from the GitHub repository.
To find the latest version of the sample schemas installation scripts, go to the following GitHub web site : https://github.com/oracle/db-sample-schemas/releases/latest
For example, If you want a 220.127.116.11 version of the scripts, then go to https://github.com/oracle/db-sample-schemas/releases/tag/v18.104.22.168
Clone the GitHub repository, or download the ZIP bundle from GitHub and extract the files.
Unzip the file.
Follow the instructions to create the schemas in the README contained in the zip file.
This section includes the following topics:
2.2.1 Resetting Sample Schemas
To reset sample schemas to their initial state, use the following syntax from the SQL*Plus command-line interface:
sqlplus system/systempw@connect_string @mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string
mksample script expects 11 parameters. Provide the password for
SYS, and for schemas
SH. Specify a temporary and a default tablespace, and make sure to end the name of the log file directory with a trailing slash.
mksample script produces several log files:
mkverify.logis the Sample Schema creation log file.
HRschema creation log file.
OEschema creation log file.
PMschema creation log file.
pm_p_lob.logis the SQL*Loader log file for
IXschema creation log file.
SHschema creation log file.
cust.logis the SQL*Loader log file for
prod.logis the SQL*Loader log file for
promo.logis the SQL*Loader log file for
sales.logis the SQL*Loader log file for
sales_ext.logis the external table log file for
In most situations, there is no difference between installing a Sample Schema for the first time or reinstalling it over a previously installed version. The
*_main.sql scripts drop the schema users and all of their objects.
2.2.2 Uninstalling Sample Schemas
If you need to remove the sample schemas from the installation, run script
drop_sch.sql on the SQL*Plus command line. This script ships with Oracle Database.
This script uses the following parameters:
systempwd is the password for
SYSTEM user and
connect_string is the connection string of the database.
Example 2-1 How to Uninstall Sample Schemas
sqlplus system/systempw@connect_string @drop_sch.sql