1
Installation

When you do a complete installation of Oracle9i, the Sample Schemas are installed automatically with the seed database. If for some reason the seed database is removed from your system, you will need to reinstall the Sample Schemas before you can duplicate the examples you find in Oracle documentation and training materials.

This chapter describes how to install the Oracle9i Sample Schemas. It contains the following sections:

• Using the Database Configuration Assistant
• Manually Installing the Oracle9i Sample Schemas
• Resetting the Sample Schemas

  Caution: By installing any of the Oracle9i Sample Schemas, you will destroy any previously installed schemas that use any of the following user names: HR OE PM SH QS QS_ADM QS_WS QS_ES QS_OS QS_CBADM QS_CB QS_CS Data contained in any of the these schemas will be lost by running any of the installation scripts described in this section. You should not use Oracle9i Sample Schemas for your personal or business data and applications. They are meant to be used for demonstration purposes only.

Using the Database Configuration Assistant

Using DBCA is by far the most intuitive and simple way to install the Sample Schemas. Step 4 of the database creation process lets you configure the Sample Schemas you wish to use in your database. The following dependencies are enforced by the Database Configuration Assistant:

• The checkbox "Example Schemas" needs to be checked for any Sample Schema to be created.
• "Oracle Spatial" needs to be selected to allow the Order Entry schema to be created.

• "Oracle Intermedia" needs to be selected to allow for the creation of the Product Media schema. You can select this option by clicking on the "Additional database configurations ..." button.
• "Oracle JVM" needs to be selected for the evaluation of materialized views and dimensions. If you intend to use these features, select this option by clicking on the "Additional database configurations ..." button.
• The Order Entry schema option requires the Human Resources option to be selected.
• The Product Media schema option requires the Order Entry option to be selected.
• The Shipping schema option requires the Order Entry option to be selected.
• Selecting "Oracle OLAP Services" with the Sales History option selected will add OLAP server metadata to the Sales History schema.

Two of the three predefined database templates shipped with the Database Configuration Assistant contain the Sample Schemas:

• OLTP database
• DSS database

Manually Installing the Oracle9i Sample Schemas

Prerequisites

The Sample Schemas that are available to you depend on the edition of Oracle you install and its configuration. Please consult the following table to see which schemas you can install:

Schema	Oracle9i Personal Edition	Oracle9i Standard Edition	Oracle9i Enterprise Edition
HR	OK	OK	OK
OE	OK	OK	OK
PM	OK	OK	OK
QS	OK	OK	OK
SH	Not available	Not available	Needs Partitioning Option installed

Schema Dependencies

Various dependencies have been established among the schemas. Therefore, you must create the schemas in the following order: HR, OE, PM, QS, and SH.

Use this sequence to create the schemas:

1. Create the HR schema.
2. Create the OE schema: The HR schema must already be present, and you must know the password for the HR schema so that you can grant HR object privileges to OE. Some HR tables are visible to the OE user through the use of private synonyms. In addition, some OE tables have foreign key relationships to HR tables.

   Note: The OE schema requires the database to be enabled for spatial data. You can accomplish this during installation or afterward using the Database Configuration Assistant.

3. Create the PM schema: Foreign key relationships require that the OE schema already exist when the PM schema is created. You need to know the password for OE to grant to PM the right to establish and use these foreign keys.

   Note: The PM schema requires the database to be enabled for the Java Virtual Machine (JVM) and interMedia. You can accomplish this during installation or afterward using the Database Configuration Assistant.

4. Create the QS schema: The shipping schema QS is based on order entry data in OE. Again, foreign key relationships require that the OE schema already be present when the QS schema is created. You need to know the password for OE to grant to QS the right to establish and use these foreign keys.
5. Create the SH schema. The SH schema logically depends on the OE schema, although there is nothing that prevents you from creating this schema on its own, without the four other schemas.

Installing the Human Resources (HR) Schema

All scripts necessary to create this schema reside in $ORACLE_HOME/demo/schema/human_resources.

You need to call only one script, hr_main.sql, to create all objects and load the data. Running hr_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts
2. Erases any previously installed HR schema
3. Creates the user HR and grants the necessary privileges
4. Connects as HR
5. Calls the following scripts:
   • hr_cre.sql to create data objects
   • hr_popul.sql to populate data objects
   • hr_idx.sql to create indexes on data objects
   • hr_code.sql to create procedural objects
   • hr_comnt.sql to create comments on tables and columns
   • hr_analz.sql to gather schema statistics
6. [Optional] A pair of scripts, sh_dn_c.sql and sh_dn_d.sql are provided as schema extension. To prepare the Human Resources schema for use with the Directory capabilities of Oracle Internet Directory, run the sh_dn_c.sql create script. If you want to return to the initial setup of the HR schema, use the script sh_dn_d.sql to erase the effects of sh_dn_c.sql and erase the column added by this extension.

The file used to drop the HR schema is hr_drop.sql.

Installing the Order Entry (OE) Schema and its Online Catalog (OC) Subschema

All scripts necessary to create this schema reside in $ORACLE_HOME/demo/schema/order_entry.

You need to call only one script, oe_main.sql, to create all objects and load the data. Running oe_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts
2. Erases any previously installed OE schema
3. Creates the user OE and grants the necessary privileges
4. Connects as OE
5. Calls the following scripts:
   • oe_cre.sql to create data, procedural, and user defined objects
   • oe_oe_p_pi.sql to populate the PRODUCT_INFORMATION table
   • oe_p_whs.sql to populate the WAREHOUSES table
   • oe_p_cus.sql to populate the CUSTOMERS table
   • oe_p_ord.sql to populate the ORDERS table
   • oe_p_itm.sql to populate the ORDER_ITEMS table
   • oe_p_inv.sql to populate the INVENTORIES table
   • oe_views.sql to create table views
   • oe_idx.sql to create indexes on data objects
   • oe_comnt.sql to create comments on tables and columns
   • oc_main.sql to create the OC (Online catalog) object-oriented subschema within OE. The oc_main.sql script calls the following scripts:
   • oc_cre.sql to create a sequence of interrelated user defined objects, object tables and views
   • oc_popul.sql to populate object tables
   • oc_comnt.sql to create comments on tables and columns
   • oe_p_pd.sql to populate the PRODUCT_DESCRIPTIONS table. Language-specific INSERT statements for product names and descriptions are stored in these files:
     • oe_p_us.sql
     • oe_p_ar.sql
     • oe_p_cs.sql
     • oe_p_d.sql
     • oe_p_dk.sql
     • oe_p_e.sql
     • oe_p_el.sql
     • oe_p_esa.sql
     • oe_p_f.sql
     • oe_p_frc.sql
     • oe_p_hu.sql
     • oe_p_i.sql
     • oe_p_iw.sql
     • oe_p_ja.sql
     • oe_p_ko.sql
     • oe_p_n.sql
     • oe_p_nl.sql
     • oe_p_pl.sql
     • oe_p_pt.sql
     • oe_p_ptb.sql
     • oe_p_ro.sql
     • oe_p_ru.sql
     • oe_p_s.sql
     • oe_p_sf.sql
     • oe_p_sk.sql
     • oe_p_th.sql
     • oe_p_tr.sql
     • oe_p_zhs.sql
     • oe_p_zht.sql
   • oe_analz.sql to gather schema statistics

The files used for dropping the OE schema and OC subschema are:

• oe_drop.sql
• oc_drop.sql

Installing The Product Media (PM) Schema

All files necessary to create this schema reside in $ORACLE_HOME/demo/schema/product_media.

You need to call only one script, pm_main.sql, to create all objects and load the data. Running pm_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts
2. Erases any previously installed PM schema
3. Creates the user PM and grants the necessary privileges
4. Connects as PM
5. Calls the following scripts:
   • pm_cre.sql

   The list of files used for populating the PM schema includes:

   • pm_p_lob.sql
   • pm_p_lob.ctl
   • pm_p_lob.dat

     Note: The SQL*Loader data file pm_p_lob.dat contains hard-coded absolute path names that have been set during installation. Before attempting to load the data in a different environment, you should first edit the path names in this file.

   • pm_p_ord.sql

The file used to drop the PM schema is pm_drop.sql.

Installing the Queued Shipping (QS) Schemas

All files necessary to create this schema reside in $ORACLE_HOME/demo/schema/shipping.

You need to call only one script, qs_main.sql, to create all objects and load the data. Running qs_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts
2. Erases any previously installed QS schema
3. Creates the user QS and grants the necessary privileges
4. Connects as QS
5. Calls the following scripts:
   • qs_adm.sql creates the Administrator schema
   • qs_cbadm.sql creates the Customer Billing Administration schema
   • qs_cre.sql creates queues, queue tables for the Queued Shipping schema
   • qs_cs.sql creates the Customer Service schema
   • qs_es.sql creates the Eastern Shipping schema
   • qs_os.sql creates the Overseas Shipping schema
   • qs_ws.sql creates the Western Shipping schema
   • qs_run.sql creates the demo application procedures and objects

The file used for dropping all queues in an orderly fashion is qs_drop.sql.

Installing the Sales History (SH) Schema

All files necessary to create this schema reside in $ORACLE_HOME/demo/schema/sales_history.

You need to call only one script, sh_main.sql, to create all objects and load the data. Running sh_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts
2. Erases any previously installed SH schema
3. Creates the user SH and grants the necessary privileges
4. Connects as SH
5. Calls the following scripts:
   • sh_cre.sql to create tables
   • sh_pop1.sql to populate the dimension tables COUNTRIES and CHANNELS
   • sh_pop2.sql to populate the dimension table TIMES
   • sh_pop3.sql to populate the remaining tables. The dimension tables PROMOTIONS, CUSTOMERS, PRODUCTS and the fact table SALES are loaded by SQL*Loader. Then, two directory paths are created inside the database to point to the load and log file locations. This allows the loading of the table COSTS by defining the file sh_sales.dat as an external table.
   • sh_idx.sql to create indexes on tables
   • sh_cons.sql to add constraints to tables
   • sh_hiera.sql to create dimensions and hierarchies
   • sh_cremv.sql to create materialized views
   • sh_comnt.sql to add comments for columns and tables
   • sh_analz.sql to gather statistics
6. [Optional] A pair of scripts, sh_olp_c.sql and sh_olp_d.sql are provided as schema extension. To prepare the Sales History schema for use with the advanced analytic capabilities of OLAP Services, run the sh_olp_c.sql create script. If you want to return to the initial setup of the SH schema, use the script sh_olp_d.sql to erase the effects of sh_olp_c.sql and reinstate dimensions as they were before.

The file used to drop the SH schema is sh_drop.sql.

Resetting the Sample Schemas

To reset the Sample Schemas to their initial state, from the SQL*Plus command-line interface, use the following syntax:

@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd qspwd shpwd

In place of the parameters systempwd, syspwd, hrpwd, oepwd, pmpwd, qspwd, and shpwd provide the passwords for SYSTEM and SYS, and the HR, OE, PM, and QS schemas.

The mksample script produces several log files located in the directory $ORACLE_HOME/demo/schema/log/. These log files include:

• mkverify.log - Sample Schema creation log file
• hr_main.log - HR schema creation log file
• oe_oc_main.log - OE schema creation log file
• pm_main.log - PM schema creation log file
• pm_p_lob.log - SQL*Loader log file from loading PM.PRINT_MEDIA
• qs_main.log - QS schema creation log file
• sh_main.log - SH schema creation log file
• sh_cust.log - SQL*Loader log file from loading SH.CUSTOMERS
• sh_prod.log - SQL*Loader log file from loading SH.PRODUCTS
• sh_promo.log - SQL*Loader log file from loading SH.PROMOTIONS
• sh_sales.log - SQL*Loader log file from loading SH.SALES
• sh_sales_ext.log - External table log file from loading SH.COSTS

  See Also: Chapter 4, "Oracle9i Sample Schema Scripts" for a copy of the mksample script

In most situations, there is no difference between installing a particular Sample Schema for the first time or reinstalling it over a previously installed version. The *_main.sql scripts drop the schema users and all their objects.

In some cases, complex inter-object relationships in the OE or QS schemas prevent the DROP USER ... CASCADE operations from completing normally. In these rare cases, go through one of the following sequences.

For the OC catalog subschema of the OE schema:

1. Connect as the user OE.
2. Execute the script oc_drop.sql.
3. Connect as SYSTEM.
4. Make sure nobody is connected as OE:

   SELECT username FROM v$session;
   
   

5. Drop the user:

   DROP USER oe CASCADE;
   
   

For the QS schemas:

1. Connect as SYSTEM.
2. Make sure nobody is connected as a QS user:

   SELECT username FROM v$session WHERE username like 'QS%';
   
   

3. Drop the schemas by executing the script qs_drop.sql. You will be prompted for the passwords for the individual users.