Oracle9i Sample Schemas Release 1 (9.0.1) Part Number A90129-01 |
|
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 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:
Two of the three predefined database templates shipped with the Database Configuration Assistant contain the Sample Schemas:
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:
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:
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:
HR
and grants the necessary privileges
The file used to drop the HR schema is hr_drop.sql
.
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:
PRODUCT_INFORMATION
table
WAREHOUSES
table
CUSTOMERS
table
ORDERS
table
ORDER_ITEMS
table
INVENTORIES
table
OC
(Online catalog) object-oriented subschema within OE
. The oc_main.sql script calls the following scripts:
oe_p_pd
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
to gather schema statistics
The files used for dropping the OE schema and OC subschema are:
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:
The list of files used for populating the PM schema includes:
The file used to drop the PM schema is pm_drop.sql
.
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:
QS
schema
QS
and grants the necessary privileges
QS
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
.
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:
SH
schema
SH
and grants the necessary privileges
SH
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
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
.
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:
OE
.
oc_drop.sql
.
SYSTEM
.
OE
:
SELECT username FROM v$session;
DROP USER oe CASCADE;
For the QS
schemas:
SYSTEM
.
QS
user:
SELECT username FROM v$session WHERE username like 'QS%';
qs_drop.sql
. You will be prompted for the passwords for the individual users.
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|