4 Sample Schema Scripts and Object Descriptions

This chapter describes the scripts used to generate the Oracle Database Sample Schemas. It contains the following sections:

About the Scripts

Sample schemas script directories are located in $ORACLE_HOME/demo/schema. You must install the Oracle Database Examples media to populate the directories with the Sample Schema scripts. Each schema has two primary scripts:

  • The xx_main.sql script, where xx is the schema abbreviation, resets and creates all objects and data for a particular schema. This main script calls all other scripts necessary to build and load the schema.

  • Script xx_drop.sql, where xx is the schema name, removes all objects from a particular schema.

Sample Schemas script directories are located in the $ORACLE_HOME/demo/schema directory after completing the Oracle Database Examples installation.

Note:

This chapter contains only the master script for the entire sample schemas environment. It does not include the scripts for the individual schemas because these scripts are very lengthy.

Master Script

The master script, mksample.sql, sets up the overall Sample Schema environment and creates all the schemas.

Note:

In the master script (mksample.sql), which follows, you will notice variables such as %s_pmPath%, %s_logPath%, and %s_shPath%. These variables are instantiated on installation.

mksample.sql

The text of the mksample.sql script follows:

Rem
Rem $Header: mksample.sql.sbs 02-apr-2003.14:55:17 $
Rem
Rem mksample.sql
Rem
Rem Copyright (c) 2001, 2003, Oracle Corporation. All rights reserved. 
Rem
Rem NAME
Rem mksample.sql - creates all 5 Sample Schemas
Rem
Rem DESCRIPTION
Rem This script rees and creates all Schemas belonging
Rem to the Oracle Database 10g Sample Schemas.
Rem If you are unsure about the prerequisites for the Sample Schemas,
Rem please use the Database Configuration Assistant DBCA to
Rem configure the Sample Schemas.
Rem
Rem NOTES
Rem - OUI instantiates this script during install and saves it
Rem as mksample.sql. The instantiated scripts matches
Rem the directory structure on your system
Rem - Tablespace EXAMPLE created with:
Rem CREATE TABLESPACE example 
Rem NOLOGGING 
Rem DATAFILE '<filename>' SIZE 150M REUSE 
Rem AUTOEXTEND ON NEXT 640k
Rem MAXSIZE UNLIMITED
Rem EXTENT MANAGEMENT LOCAL
Rem SEGMENT SPACE MANAGEMENT AUTO;
Rem 
Rem - CAUTION: This script will erase the following schemas:
Rem - HR
Rem - OE
Rem - PM
Rem - SH
Rem - IX
Rem - BI
Rem - CAUTION: Never use the preceding Sample Schemas for
Rem anything other than demos and examples
Rem - USAGE: To return the Sample Schemas to their initial 
Rem state, you can call this script and pass the passwords
Rem for SYS, SYSTEM and the schemas as parameters.
Rem Example: @?/demo/schema/mksample mgr secure h1 o2 p3 q4 s5
Rem (please choose your own passwords for security purposes)
Rem
Rem MODIFIED (MM/DD/YY)
Rem 
Rem

SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 999
SET ECHO OFF
SET CONCAT '.'
SET SHOWMODE OFF

PROMPT 
PROMPT specify password for SYSTEM as parameter 1:
DEFINE password_system = &1
PROMPT 
PROMPT specify password for SYS as parameter 2:
DEFINE password_sys = &2
PROMPT 
PROMPT specify password for HR as parameter 3:
DEFINE password_hr = &3
PROMPT
PROMPT specify password for OE as parameter 4:
DEFINE password_oe = &4
PROMPT
PROMPT specify password for PM as parameter 5:
DEFINE password_pm = &5
PROMPT
PROMPT specify password for IX as parameter 6:
DEFINE password_ix = &6
PROMPT
PROMPT specify password for SH as parameter 7:
DEFINE password_sh = &7
PROMPT 
PROMPT specify password for BI as parameter 8:
DEFINE password_bi = &8
PROMPT 
PROMPT specify default tablespace as parameter 9:
DEFINE default_ts = &9
PROMPT
PROMPT specify temporary tablespace as parameter 10:
DEFINE temp_ts = &10
PROMPT 
PROMPT specify log file directory (including trailing delimiter) as parameter
 11:
DEFINE logfile_dir = &11
PROMPT 
PROMPT Sample Schemas are being created ...
PROMPT
DEFINE vrs = v3

CONNECT system/&&password_system

DROP USER hr CASCADE;
DROP USER oe CASCADE;
DROP USER pm CASCADE;
DROP USER ix CASCADE;
DROP USER sh CASCADE;
DROP USER bi CASCADE;

CONNECT system/&&password_system

SET SHOWMODE OFF

@?/demo/schema/human_resources/hr_main.sql &&password_hr &&default_ts &&temp_ts
 &&password_sys &&logfile_dir

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/order_entry/oe_main.sql &&password_oe &&default_ts &&temp_ts
 &&password_hr &&password_sys %s_oePath% &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/product_media/pm_main.sql &&password_pm &&default_ts &&temp_ts
 &&password_oe &&password_sys %s_pmPath% &&logfile_dir %s_pmPath%

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/info_exchange/ix_main.sql &&password_ix &&default_ts &&temp_ts
 &&password_sys &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/sales_history/sh_main &&password_sh &&default_ts &&temp_ts
 &&password_sys %s_shPath% &&logfile_dir &vrs

CONNECT system/&&password_system
SET SHOWMODE OFF

@?/demo/schema/bus_intelligence/bi_main &&password_bi &&default_ts &&temp_ts
 &&password_sys &&password_oe &&password_sh &&logfile_dir &vrs

CONNECT system/&&password_system

SPOOL OFF

DEFINE veri_spool = &&logfile_dir.mkverify_&vrs..log

@?/demo/schema/mkverify &&password_system &veri_spool 

EXIT

HR Schema

This section lists the names of the scripts that create the human resources (HR) schema and describes the objects in the schema. Table 4-1 lists the HR scripts in alphabetical order, while Table 4-2 lists its objects.

Table 4-1 HR Schema Scripts

Script Name Description

hr_analz.sql

Collects statistics on the tables in the schema

hr_code.sql

Creates procedural objects in the schema

hr_comnt.sql

Creates comments for each object in the schema

hr_cre.sql

Creates the HR objects

hr_dn_c.sql

Adds the distinguished name column used by Oracle Internet Directory to the employees and departments tables

hr_dn_d.sql

Drops the Oracle Internet Directory distinguished name column from employees and departments

hr_drop.sql

Drops schema HR and all its objects

hr_idx.sql

Creates indexes on the HR tables

hr_main.sql

Main script for schema HR ; calls other scripts

hr_popul.sql

Populates the objects


Table 4-2 HR Objects

Object Type Objects

Index

COUNTRY_C_ID_PK, DEPT_ID_PK, DEPT_LOCATION_IX, EMP_DEPARTMENT_IX, EMP_EMAIL_UK, EMP_EMP_ID_PK, EMP_JOB_IX, EMP_MANAGER_IX, EMP_NAME_IX, JHIST_DEPARTMENT_IX, JHIST_EMPLOYEE_IX, JHIST_EMP_ID_ST_DATE_PK, JHIST_JOB_IX, JOB_ID_PK, LOC_CITY_IX, LOC_COUNTRY_IX, LOC_ID_PK, LOC_STATE_PROVINCE_IX, REG_ID_PK

Procedure

ADD_JOB_HISTORY, SECURE_DML

Sequence

DEPARTMENTS_SEQ, EMPLOYEES_SEQ, LOCATIONS_SEQ

Table

COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY, LOCATIONS, REGIONS

Trigger

SECURE_EMPLOYEES, UPDATE_JOB_HISTORY

View

EMP_DETAILS_VIEW


HR Table Descriptions

This section describes the columns of each table of schema HR.

Table 4-3 HR.COUNTRIES Table Description

Column Name Null? Type

COUNTRY_ID

NOT NULL

CHAR(2)

COUNTRY_NAME

 

VARCHAR2(40)

REGION_ID

 

NUMBER


Table 4-4 HR.DEPARTMENTS Table Description

Column Name Null? Type

DEPARTMENT_ID

NOT NULL

NUMBER(4)

DEPARTMENT_NAME

NOT NULL

VARCHAR2(30)

MANAGER_ID

 

NUMBER(6)

LOCATION_ID

 

NUMBER(4)


Table 4-5 HR.EMPLOYEES Table Description

Column Name Null? Type

EMPLOYEE_ID

NOT NULL

NUMBER(6)

FIRST_NAME

 

VARCHAR2(20)

LAST_NAME

NOT NULL

VARCHAR2(25)

EMAIL

NOT NULL

VARCHAR2(20)

PHONE_NUMBER

 

VARCHAR2(20)

HIRE_DATE

NOT NULL

DATE

JOB_ID

NOT NULL

VARCHAR2(10)

SALARY

 

NUMBER(8,2)

COMMISSION_PCT

 

NUMBER(2,2)

MANAGER_ID

 

NUMBER(6)

DEPARTMENT_ID

 

NUMBER(4)


Table 4-6 HR.JOBS Table Description

Column Name Null? Type

JOB_ID

NOT NULL

VARCHAR2(10)

JOB_TITLE

NOT NULL

VARCHAR2(35)

MIN_SALARY

 

NUMBER(6)

MAX_SALARY

 

NUMBER(6)


Table 4-7 HR.JOB_HISTORY Table Description

Column Name Null? Type

EMPLOYEE_ID

NOT NULL

NUMBER(6)

START_DATE

NOT NULL

DATE

END_DATE

NOT NULL

DATE

JOB_ID

NOT NULL

VARCHAR2(10)

DEPARTMENT_ID

 

NUMBER(4)


Table 4-8 HR.LOCATIONS Table Description

Column Name Null? Type

LOCATION_ID

NOT NULL

NUMBER(4)

STREET_ADDRESS

 

VARCHAR2(40)

POSTAL_CODE

 

VARCHAR2(12)

CITY

NOT NULL

VARCHAR2(30)

STATE_PROVINCE

 

VARCHAR2(25)

COUNTRY_ID

 

CHAR(2)


Table 4-9 HR.REGIONS Table Description

Column Name Null? Type

REGION_ID

NOT NULL

NUMBER

REGION_NAME

 

VARCHAR2(25)


OE Schema

This section lists the names of the scripts that create the Order Entry (OE) sample schema and describes the objects in the schema. Table 4-10 lists the OE scripts in alphabetical order, while Table 4-11 lists its objects. Note that language-specific statements for product names and descriptions are stored in these files (each representing a different language): INSERToe_p_us.sqloe_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.

Table 4-10 OE Schema Scripts

Script Name Description

oc_comnt.sql

Adds comments to the online catalog (OC) subschema wherever possible

oc_cre.sql

Creates subschema OC

oc_drop.sql

Drops subschema OC

oc_main.sql

Main script for subschema OC

oc_popul.sqla

Populates the object tables

oe_analz.sql

Gathers statistics on the OE objects

oe_comnt.sql

Creates comments for the objects in the schema

oe_cre.sql

Creates the OE objects

oe_drop.sql

Drops schema OE and all its objects

oe_idx.sql

Creates indexes on the OE tables

oe_main.sql

Main script for the OE schema; calls other scripts

oe_views.sql

Creates the OE schema views


Table 4-11 OE Objects

Object Type Objects

Index

CUSTOMERS_PK, CUST_ACCOUNT_MANAGER_IX, CUST_EMAIL_IX, CUST_LNAME_IX, CUST_UPPER_NAME_IX, INVENTORY_IX, INV_PRODUCT_IX, ITEM_ORDER_IX, ITEM_PRODUCT_IX, ORDER_ITEMS_PK, ORDER_ITEMS_UK, ORDER_PK, ORD_CUSTOMER_IX, ORD_ORDER_DATE_IX, ORD_SALES_REP_IX, PRD_DESC_PK, PRODUCT_INFORMATION_PK, PROD_NAME_IX, PROD_SUPPLIER_IX, PROMO_ID_PK, REFERENCE_IS_UNIQUE, SYS_C003584, SYS_C003587, SYS_C003588, SYS_C003589, SYS_C003590, WAREHOUSES_PK, WHS_LOCATION_IX

Function

GET_PHONE_NUMBER_F

Sequence

ORDERS_SEQ

Lob

SYS_LOB0000045843C00022$$, SYS_LOB0000045843C00023$$, SYS_LOB0000045852C00003$$, SYS_LOB0000045852C00012$$, SYS_LOB0000045852C00013$$, SYS_LOB0000046019C00004$$, SYS_LOB0000046019C00005$$, SYS_LOB0000046019C00007$$, SYS_LOB0000046019C00011$$, SYS_LOB0000046019C00012$$, SYS_LOB0000046019C00015$$, SYS_LOB0000046019C00024$$, SYS_LOB0000046019C00031$$, SYS_LOB0000046019C00032$$, SYS_LOB0000046044C00003$$

Synonym

COUNTRIES, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY, LOCATIONS

Table

CUSTOMERS, INVENTORIES, ORDERS, ORDER_ITEMS, PRODUCT_DESCRIPTIONS, PRODUCT_INFORMATION, WAREHOUSES

Trigger

INSERT_ORD_LINE, ORDERS_ITEMS_TRG, ORDERS_TRG

Type

CATALOG_TYP, CATEGORY_TYP, COMPOSITE_CATEGORY_TYP, CORPORATE_CUSTOMER_TYP, CUSTOMER_TYP, CUST_ADDRESS_TYP, INVENTORY_LIST_TYP, INVENTORY_TYP, LEAF_CATEGORY_TYP, ORDER_ITEM_LIST_TYP, ORDER_ITEM_TYP, ORDER_LIST_TYP, ORDER_TYP, PHONE_LIST_TYP, PRODUCT_INFORMATION_TYP, PRODUCT_REF_LIST_TYP, SUBCATEGORY_REF_LIST_TYP, SYS_YOID0000046073$, SYS_YOID0000046075$, SYS_YOID0000046077$, SYS_YOID0000046079$, SYS_YOID0000046081$, WAREHOUSE_TYP, XDBPO_ACTIONS_TYPE, XDBPO_ACTION_COLLECTION, XDBPO_ACTION_TYPE, XDBPO_LINEITEMS_TYPE, XDBPO_LINEITEM_COLLECTION, XDBPO_LINEITEM_TYPE, XDBPO_PART_TYPE, XDBPO_REJECTION_TYPE, XDBPO_SHIPINSTRUCTIONS_TYPE, XDBPO_TYPE

Type Body

CATALOG_TYP, COMPOSITE_CATEGORY_TYP, LEAF_CATEGORY_TYP

View

ACCOUNT_MANAGERS, BOMBAY_INVENTORY, CUSTOMERS_VIEW, DEPTVIEW, OC_CORPORATE_CUSTOMERS, OC_CUSTOMERS, OC_INVENTORIES, OC_ORDERS, OC_PRODUCT_INFORMATION, ORDERS_VIEW, PRODUCTS, PRODUCT_PRICES, SYDNEY_INVENTORY, TORONTO_INVENTORY


OE Table Descriptions

This section describes the tables of sample database schema OE.

Table OE.CUSTOMERS

Table 4-12 OE.CUSTOMERS Table Description

Column Name Null? Type

CUSTOMER_ID

NOT NULL

NUMBER(6)

CUST_FIRST_NAME

NOT NULL

VARCHAR2(20)

CUST_LAST_NAME

NOT NULL

VARCHAR2(20)

CUST_ADDRESS

 

CUST_ADDRESS_TYP

PHONE_NUMBERS

 

PHONE_LIST_TYP

NLS_LANGUAGE

 

VARCHAR2(3)

NLS_TERRITORY

 

VARCHAR2(30)

CREDIT_LIMIT

 

NUMBER(9,2)

CUST_EMAIL

 

VARCHAR2(30)

ACCOUNT_MGR_ID

 

NUMBER(6)

CUST_GEO_LOCATION

 

MDSYS.SDO_GEOMETRY

DATE_OF_BIRTH

 

DATE

MARITAL_STATUS

 

VARCHAR2(20)

GENDER

 

VARCHAR2(1)

INCOME_LEVEL

 

VARCHAR2(20)


Table OE.INVENTORIES

Table 4-13 OE.INVENTORIES Table Description

Column Name Null? Type

PRODUCT_ID

NOT NULL

NUMBER(6)

WAREHOUSE_ID

NOT NULL

NUMBER(3)

QUANTITY_ON_HAND

NOT NULL

NUMBER(8)


Table OE.ORDERS

Table 4-14 OE.ORDERS Table Description

Column Name Null? Type

ORDER_ID

NOT NULL

NUMBER(12)

ORDER_DATE

NOT NULL

TIMESTAMP(6) WITH LOCAL TIME ZONE

ORDER_MODE

 

VARCHAR2(8)

CUSTOMER_ID

NOT NULL

NUMBER(6)

ORDER_STATUS

 

NUMBER(2)

ORDER_TOTAL

 

NUMBER(8,2)

SALES_REP_ID

 

NUMBER(6)

PROMOTION_ID

 

NUMBER(6)


Table OE.ORDER_ITEMS

Table 4-15 OE.ORDER_ITEMS Table Description

Column Name Null? Type

ORDER_ID

NOT NULL

NUMBER(12)

LINE_ITEM_ID

NOT NULL

NUMBER(3)

PRODUCT_ID

NOT NULL

NUMBER(6)

UNIT_PRICE

 

NUMBER(8,2)

QUANTITY

 

NUMBER(8)


Table OE.PRODUCT_DESCRIPTIONS

Table 4-16 OE.PRODUCT_DESCRIPTIONS Table Description

Column Name Null? Type

PRODUCT_ID

NOT NULL

NUMBER(6)

LANGUAGE_ID

NOT NULL

VARCHAR2(3)

TRANSLATED_NAME

NOT NULL

NVARCHAR2(50)

TRANSLATED_DESCRIPTION

NOT NULL

NVARCHAR2(2000)


Table OE.PRODUCT_INFORMATION

Table 4-17 OE.PRODUCT_INFORMATION Table Description

Column Name Null? Type

PRODUCT_ID

NOT NULL

NUMBER(6)

PRODUCT_NAME

 

VARCHAR2(50)

PRODUCT_DESCRIPTION

 

VARCHAR2(2000)

CATEGORY_ID

 

NUMBER(2)

WEIGHT_CLASS

 

NUMBER(1)

WARRANTY_PERIOD

 

INTERVAL YEAR(2) TO MONTH

SUPPLIER_ID

 

NUMBER(6))

PRODUCT_STATUS

 

VARCHAR2(20)

LIST_PRICE

 

NUMBER(8,2)

MIN_PRICE

 

NUMBER(8,2)

CATALOG_URL

 

VARCHAR2(50)


Table OE.WAREHOUSES

Table 4-18 OE.WAREHOUSES Table Description

Column Name Null? Type

WAREHOUSE_ID

NOT NULL

NUMBER(3)

WAREHOUSE_SPEC

 

SYS.XMLTYPE

WAREHOUSE_NAME

 

VARCHAR2(35)

LOCATION_ID

 

NUMBER(4)

WH_GEO_LOCATION

 

MDSYS.SDO_GEOMETRY


Column warehouse_spec of table OE.warehouses contains XMLType data. This data is not based on any XML schema, which means that it can take any form. However, the actual data in column warehouse_spec at the outset (before any changes you might have made to it) has a top-level element Warehouse with the following child elements:

  • Building, with text node Owned or Rented

  • Area, with text node a number (representing, for example, square feet)

  • Docks, with text node the number of loading docks (for example, 1, 2, or 3)

  • DockType, with text node empty or Rear Load or Side Load

  • WaterAccess, with text node Y or N

  • RailAccess, with text node Y or N

  • Parking, with text node Street or Lot

  • VClearance (vertical clearance), with text node a number followed by a linear unit (for example, 11.5 ft)

See Also:

Oracle XML DB Developer's Guide for examples using the XMLType data in column warehouse_spec

Table OE.PURCHASEORDER

Table OE.purchaseorder is an object-relational table with XMLType data. The data conforms to XML schema purchaseOrder.xsd — see Appendix A, "Purchase-Order XML Schema".

PM Schema

This section lists the names of the scripts that create the Product Media (PM) schema and describes the objects in the schema. Table 4-19 lists the OE scripts in alphabetical order, while Table 4-20 lists its objects. Note that 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.

Table 4-19 PM Schema Scripts

Script Name Description

pm_analz.sql

Gathers statistics on the PM objects

pm_cre.sql

Creates the PM objects

pm_drop.sql

Drops schema PM and all its objects

pm_p_ord.sql, pm_p_lob.sql, pm_p_lob.ctl, pm_p_lob.dat

Populates the objects in the schema

pm_main.sql

Main script for schema PM, which calls other scripts


Table 4-20 PM Objects

Object Type Objects

Index

ONLINEMEDIA_PK, PRINTMEDIA_PK, SYS_C003538

Lob

SYS_LOB0000045882C00003$$, SYS_LOB0000045882C00017$$, SYS_LOB0000045882C00019$$, SYS_LOB0000045882C00034$$, SYS_LOB0000045882C00042$$, SYS_LOB0000045882C00054$$, SYS_LOB0000045882C00062$$, SYS_LOB0000045882C00069$$, SYS_LOB0000045882C00071$$, SYS_LOB0000045882C00080$$, SYS_LOB0000045907C00003$$, SYS_LOB0000045907C00004$$, SYS_LOB0000045907C00005$$, SYS_LOB0000045907C00006$$, SYS_LOB0000045907C00009$$, SYS_LOB0000045907C00015$$, SYS_LOB0000045908C00004$$

Table

ONLINE_MEDIA, PRINT_MEDIA

Type

ADHEADER_TYP, TEXTDOC_TAB, TEXTDOC_TYP


PM Table Descriptions

This section describes the columns of each table of schema PM, as follows:

Table 4-21 PM.ONLINE_MEDIA Table Description

Column Name Null? Type

PRODUCT_ID

NOT NULL

NUMBER(6)

PRODUCT_PHOTO

 

ORDSYS.ORDIMAGE

PRODUCT_PHOTO_SIGNATURE

 

ORDSYS.ORDIMAGESIGNATURE

This type, ORDImageSignature, is deprecated; do not use it in new code. Existing occurrences of this object type will continue to function as in the past.

PRODUCT_THUMBNAIL

 

ORDSYS.ORDIMAGE

PRODUCT_VIDEO

 

ORDSYS.ORDVIDEO

PRODUCT_AUDIO

 

ORDSYS.ORDAUDIO

PRODUCT_TEXT

 

CLOB

PRODUCT_TESTIMONIALS

 

ORDSYS.ORDDOC


Table 4-22 PM.PRINT_MEDIA Table Description

Column Name Null? Type

PRODUCT_ID

NOT NULL

NUMBER(6)

AD_ID

NOT NULL

NUMBER(6)

AD_COMPOSITE

 

BLOB

AD_SOURCETEXT

 

CLOB

AD_FINALTEXT

 

CLOB

AD_FLTEXTN

 

NCLOB

AD_TEXTDOCS_NTAB

 

TEXTDOC_TAB

AD_PHOTO

 

BLOB

AD_GRAPHIC

 

BINARY FILE LOB

AD_HEADER

 

ADHEADER_TYP


IX Schema

This section lists the names of the scripts that create the Information Exchange (IX) schema group and describes the objects in the schemas. Table 4-23 lists the IX scripts in alphabetical order, while Table 4-24 lists its objects.

Table 4-23 Information Exchange (IX) Schema Scripts

Script Name Description

cix_v3.sql

Creates the IX schema objects

dix_v3.sql

Drops schema IX objects

ix_main.sql

Main script for schema IX; calls other scripts

vix_v3.sql

Enables, disables, and verifies IX objects


Table 4-24 IX Objects

Object Type Objects

Evaluation Context

AQ$_ORDERS_QUEUETABLE_V, AQ$_STREAMS_QUEUE_TABLE_V

Index

SYS_C003540, SYS_C003543, SYS_C003548, SYS_C003551, SYS_IOT_TOP_45932, SYS_IOT_TOP_45934, SYS_IOT_TOP_45936, SYS_IOT_TOP_45939, SYS_IOT_TOP_45949, SYS_IOT_TOP_45951, SYS_IOT_TOP_45953, SYS_IOT_TOP_45956

Lob

SYS_LOB0000045926C00036$$, SYS_LOB0000045941C00028$$, SYS_LOB0000045941C00029$$

Queue

AQ$_ORDERS_QUEUETABLE_E, AQ$_STREAMS_QUEUE_TABLE_E, ORDERS_QUEUE, STREAMS_QUEUE

Rule Set

ORDERS_QUEUE_N, ORDERS_QUEUE_R, STREAMS_QUEUE_N, STREAMS_QUEUE_R

Sequence

AQ$_ORDERS_QUEUETABLE_N, AQ$_STREAMS_QUEUE_TABLE_N

Table

ORDERS_QUEUETABLE, STREAMS_QUEUE_TABLE

Type

ORDER_EVENT_TYP

View

AQ$ORDERS_QUEUETABLE, AQ$ORDERS_QUEUETABLE_R, AQ$ORDERS_QUEUETABLE_S, AQ$STREAMS_QUEUE_TABLE, AQ$STREAMS_QUEUE_TABLE_R, AQ$STREAMS_QUEUE_TABLE_S


IX Table Descriptions

This section describes the columns of each table of schema IX.

Table 4-25 IX.ORDERS_QUEUETABLE Table Description

Column Name Null? Type

Q_NAME

 

VARCHAR2(30)

MSGID

NOT NULL

RAW(16)

CORRID

 

VARCHAR2(128)

PRIORITY

 

NUMBER

STATE

 

NUMBER

DELAY

 

TIMESTAMP(6)

EXPIRATION

 

NUMBER

TIME_MANAGER_INFO

 

TIMESTAMP(6)

LOCAL_ORDER_NO

 

NUMBER

CHAIN_NO

 

NUMBER

CSCN

 

NUMBER

DSCN

 

NUMBER

ENQ_TIME

 

TIMESTAMP(6)

ENQ_UID

 

VARCHAR2(30)

ENQ_TID

 

VARCHAR2(30)

DEQ_TIME

 

TIMESTAMP(6)

EEQ_UID

 

VARCHAR2(30)

DEQ_TID

 

VARCHAR2(30)

RETRY_COUNT

 

NUMBER

EXCEPTION_QSCHEMA

 

VARCHAR2(30)

EXCEPTION_QUEUE

 

VARCHAR2(30)

STEP_NO

 

NUMBER

RECIPIENT_KEY

 

NUMBER

DEQUEUE_MSGID

 

RAW(16)

SENDER_NAME

 

VARCHAR2(30)

SENDER_ADDRESS

 

VARCHAR2(1024)

SENDER_PROTOCOL

 

NUMBER

USER_DATA

 

ORDER_EVENT_TYP

USER_PROP

 

SYS.ANYDATA


Table 4-26 IX.STREAMS_QUEUE_TABLE Table Description

Column Name Null? Type

Q_NAME

 

VARCHAR2(30)

MSGID

NOT NULL

RAW(16)

CORRID

 

VARCHAR2(128)

PRIORITY

 

NUMBER

STATE

 

NUMBER

DELAY

 

TIMESTAMP(6)

EXPIRATION

 

NUMBER

TIME_MANAGER_INFO

 

TIMESTAMP(6)

LOCAL_ORDER_NO

 

NUMBER

CHAIN_NO

 

NUMBER

CSCN

 

NUMBER

DSCN

 

NUMBER

ENQ_TIME

 

TIMESTAMP(6)

ENQ_UID

 

VARCHAR2(30)

ENQ_TID

 

VARCHAR2(30)

DEQ_TIME

 

TIMESTAMP(6)

EEQ_UID

 

VARCHAR2(30)

DEQ_TID

 

VARCHAR2(30)

RETRY_COUNT

 

NUMBER

EXCEPTION_QSCHEMA

 

VARCHAR2(30)

EXCEPTION_QUEUE

 

VARCHAR2(30)

STEP_NO

 

NUMBER

RECIPIENT_KEY

 

NUMBER

DEQUEUE_MSGID

 

RAW(16)

SENDER_NAME

 

VARCHAR2(30)

SENDER_ADDRESS

 

VARCHAR2(1024)

SENDER_PROTOCOL

 

NUMBER

USER_DATA

 

ORDER_EVENT_TYP

USER_PROP

 

SYS.ANYDATA


SH Schema

This section lists the names of the scripts that create the Sales History (SH) schema and describes the objects in the schema. Table 4-27 lists the SH scripts in alphabetical order, while Table 4-28 lists its objects.

Table 4-27 SH Schema Scripts

Script Name Description

sh_analz.sql

Gathers statistics on the schema objects

sh_comnt.sql

Creates comments for the objects in the schema

sh_cons.sql

Modifies constraints on objects in the schema

sh_cre.sql

Creates the objects in the schema

sh_cremv.sql

Creates materialized views and bitmapped indexes

sh_drop.sql

Drops schema SH and all its objects

sh_idx.sql

Creates indexes on tables in the schema

sh_main.sql

Main script for schema SH; calls other scripts

olp_v3.sql

Creates dimensions and hierarchies used by the OLAP server

sh_olp_d.sql

Drops the objects used by the OLAP server


Table 4-28 SH Objects

Object Type Objects

Dimension

CHANNELS_DIM, CUSTOMERS_DIM, PRODUCTS_DIM, PROMOTIONS_DIM, TIMES_DIM

Index

CHANNELS_PK, COSTS_PROD_BIX, COSTS_TIME_BIX, COUNTRIES_PK, CUSTOMERS_GENDER_BIX, CUSTOMERS_MARITAL_BIX, CUSTOMERS_PK, CUSTOMERS_YOB_BIX, DR$SUP_TEXT_IDX$X, FW_PSC_S_MV_CHAN_BIX, FW_PSC_S_MV_PROMO_BIX, FW_PSC_S_MV_SUBCAT_BIX, FW_PSC_S_MV_WD_BIX, PRODUCTS_PK, PRODUCTS_PROD_CAT_IX, PRODUCTS_PROD_STATUS_BIX, PRODUCTS_PROD_SUBCAT_IX, PROMO_PK, SALES_CHANNEL_BIX, SALES_CUST_BIX, SALES_PROD_BIX, SALES_PROMO_BIX, SALES_TIME_BIX, SUP_TEXT_IDX, SYS_IOT_TOP_45927, SYS_IOT_TOP_45932, TIMES_PK

Index Partition

COSTS_PROD_BIX, COSTS_TIME_BIX, SALES_CHANNEL_BIX, SALES_CUST_BIX, SALES_PROD_BIX, SALES_PROMO_BIX, SALES_TIME_BIX

Lob

SYS_LOB0000045924C00006$$, SYS_LOB0000045929C00002$$

Materialized View

CAL_MONTH_SALES_MV, FWEEK_PSCAT_SALES_MV

Table

CHANNELS, COSTS, COUNTRIES, CUSTOMERS, PRODUCTS, PROMOTIONS, SALES, TIMES

Table Partition

COSTS, SALES

View

PROFITS


SH Table Descriptions

This section describes the columns of each table of schema SH, as follows:

Table 4-29 SH.CHANNELS Table Description

Column Name Null? Type

CHANNEL_ID

NOT NULL

NUMBER

CHANNEL_DESC

NOT NULL

VARCHAR2(20)

CHANNEL_CLASS

NOT NULL

VARCHAR2(20)

CHANNEL_CLASS_ID

NOT NULL

NUMBER

CHANNEL_TOTAL

NOT NULL

VARCHAR2(13)

CHANNEL_TOTAL_ID

NOT NULL

NUMBER


Table 4-30 SH.COSTS Table Description

Column Name Null? Type

PROD_ID

NOT NULL

NUMBER

TIME_DESC

NOT NULL

DATE

PROMO_ID

NOT NULL

NUMBER

CHANNEL_ID

NOT NULL

NUMBER

UNIT_COST

NOT NULL

NUMBER(10,2)

UNIT_PRICE

NOT NULL

NUMBER(10,2)


Table 4-31 SH.COUNTRIES Table Description

Column Name Null? Type

COUNTRY_ID

NOT NULL

NUMBER

COUNTRY_ISO_CODE

NOT NULL

CHAR(2)

COUNTRY_NAME

NOT NULL

VARCHAR2(40)

COUNTRY_SUBREGION

NOT NULL

VARCHAR2(30)

COUNTRY_SUBREGION_ID

NOT NULL

NUMBER

COUNTRY_REGION

NOT NULL

VARCHAR2(20)

COUNTRY_REGION_ID

NOT NULL

NUMBER

COUNTRY_TOTAL

NOT NULL

VARCHAR2(11)

COUNTRY_TOTAL_ID

NOT NULL

NUMBER

COUNTRY_NAME_HIST

 

VARCHAR2(40)


Table 4-32 SH.CUSTOMERS Table Description

Column Name Null? Type

CUST_ID

NOT NULL

NUMBER

CUST_FIRST_NAME

NOT NULL

VARCHAR2(20)

CUST_LAST_NAME

NOT NULL

VARCHAR2(40)

CUST_GENDER

NOT NULL

CHAR(1)

CUST_YEAR_OF_BIRTH

NOT NULL

NUMBER(4)

CUST_MARITAL_STATUS

 

VARCHAR2(20)

CUST_STREET_ADDRESS

NOT NULL

VARCHAR2(40)

CUST_POSTAL_CODE

NOT NULL

VARCHAR2(10)

CUST_CITY

NOT NULL

VARCHAR2(30)

CUST_CITY_ID

NOT NULL

NUMBER

CUST_STATE_PROVINCE

NOT NULL

VARCHAR2(40)

CUST_STATE_PROVINCE_ID

NOT NULL

NUMBER

COUNTRY_ID

NOT NULL

NUMBER

CUST_MAIN_PHONE_NUMBER

NOT NULL

VARCHAR2(25)

CUST_INCOME_LEVEL

 

VARCHAR2(30)

CUST_CREDIT_LIMIT

 

NUMBER

CUST_EMAIL

 

VARCHAR2(30)

CUST_TOTAL

NOT NULL

VARCHAR2(14)

CUST_TOTAL_ID

NOT NULL

NUMBER

CUST_SRC_ID

 

NUMBER

CUST_EFF_FROM

 

DATE

CUST_EFF_TO

 

DATE

CUST_VALID

 

VARCHAR2(1)


Table 4-33 SH.PRODUCTS Table Description

Column Name Null? Type

PROD_ID

NOT NULL

NUMBER(6)

PROD_NAME

NOT NULL

VARCHAR2(50)

PROD_DESC

NOT NULL

VARCHAR2(4000)

PROD_SUBCATEGORY

NOT NULL

VARCHAR2(50)

PROD_SUBCATEGORY_ID

NOT NULL

NUMBER

PROD_SUBCATEGORY_DESC

NOT NULL

VARCHAR2(2000)

PROD_CATEGORY

NOT NULL

VARCHAR2(50)

PRD_CATEGORY_ID

NOT NULL

NUMBER

PROD_CATEGORY_DESC

NOT NULL

VARCHAR2(2000)

PROD_WEIGHT_CLASS

NOT NULL

NUMBER(3)

PROD_UNIT_OF_MEASURE

 

VARCHAR2(20)

PRD_PACK_SIZE

NOT NULL

VARCHAR2(30)

PROD_SUPPLIER_ID

NOT NULL

NUMBER(6)

PROD_STATUS

NOT NULL

VARCHAR2(20)

PROD_LIST_PRICE

NOT NULL

NUMBER(8,2)

PRD_MIN_PRICE

NOT NULL

NUMBER(8,2)

PROD_TOTAL

NOT NULL

VARCHAR2(13)

PROD_TOTAL_ID

NOT NULL

NUMBER

PROD_SRC_ID

 

NUMBER

PRD_EFF_FROM

 

DATE

PROD_EFF_TO

 

DATE

PROD_VALID

 

VARCHAR2(1)


Table 4-34 SH.PROMOTIONS Table Description

Column Name Null? Type

PROMO_ID

NOT NULL

NUMBER(6)

PROMO_NAME

NOT NULL

VARCHAR2(30)

PROMO_SUBCATEGORY

NOT NULL

VARCHAR2(30)

PROMO_SUBCATEGORY_ID

NOT NULL

NUMBER

PROMO_CATEGORY

NOT NULL

VARCHAR2(30)

PRMO_CATEGORY_ID

NOT NULL

NUMBER

PROMO_COST

NOT NULL

NUMBER(10,2)

PROMO_BEGIN_DATE

NOT NULL

DATE

PROMO_END_DATE

NOT NULL

DATE

PROMO_TOTAL

NOT NULL

VARCHAR2(15)

PROMO_TOTAL_ID

NOT NULL

NUMBER


Table 4-35 SH.SALES Table Description

Column Name Null? Type

PROD_ID

NOT NULL

NUMBER

CUST_ID

NOT NULL

NUMBER

TIME_ID

NOT NULL

DATE

CHANNEL_ID

NOT NULL

NUMBER

PROMO_ID

NOT NULL

NUMBER

QUANTITY_SOLD

NOT NULL

NUMBER(10,2)

AMOUNT_SOLD

NOT NULL

NUMBER(10,2)


Table 4-36 SH.TIMES Table Description

Column Name Null? Type

TIME_ID

NOT NULL

DATE

DAY_NAME

NOT NULL

VARCHAR2(9)

DAY_NUMBER_IN_WEEK

NOT NULL

NUMBER(1)

DAY_NUMBER_IN_MONTH

NOT NULL

NUMBER(2)

CALENDAR_WEEK_NUMBER

NOT NULL

NUMBER(2)

FISCAL_WEEK_NUMBER

NOT NULL

NUMBER(2)

WEEK_ENDING_DAY

NOT NULL

DATE

WEEK_ENDING_DAY_ID

NOT NULL

NUMBER

CALENDAR_MONTH_NUMBER

NOT NULL

NUMBER(2)

FISCAL_MONTH_NUMBER

NOT NULL

NUMBER(2)

CALENDAR_MONTH_DESC

NOT NULL

VARCHAR2(8)

CALENDAR_MONTH_ID

NOT NULL

NUMBER

FISCAL_MONTH_DESC

NOT NULL

VARCHAR2(8)

FISCAL_MONTH_ID

NOT NULL

NUMBER

DAYS_IN_CAL_MONTH

NOT NULL

NUMBER

DAYS_IN_FIS_MONTH

NOT NULL

NUMBER

END_OF_CAL_MONTH

NOT NULL

DATE

END_OF_FIS_MONTH

NOT NULL

DATE

CALENDAR_MONTH_NAME

NOT NULL

VARCHAR2(9)

FISCAL_MONTH_NAME

NOT NULL

VARCHAR2(9)

CALENDAR_QUARTER_DESC

NOT NULL

CHAR(7)

CALENBDAR_QUARTER_ID

NOT NULL

NUMBER

FISCAL_QUARTER_DESC

NOT NULL

CHAR(7)

FISCAL_QUARTER_ID

NOT NULL

NUMBER

DAYS_IN_CAL_QUARTER

NOT NULL

NUMBER

DAYS_IN_FIS_QUARTER

NOT NULL

NUMBER

END_OF_CAL_QUARTER

NOT NULL

DATE

END_OF_FIS_QUARTER

NOT NULL

DATE

CALENDAR_QUARTER_NUMBER

NOT NULL

NUMBER(1)

FISCAL_QUARTER_NUMBER

NOT NULL

NUMBER(1)

CALENDAR_YEAR

NOT NULL

NUMBER(4)

CALENDAR_YEAR_ID

NOT NULL

NUMBER

FISCAL_YEAR

NOT NULL

NUMBER(4)

FISCAL_YEAR_ID

NOT NULL

NUMBER

DAYS_IN_CAL_YEAR

NOT NULL

NUMBER

DAYS_IN_FIS_YEAR

NOT NULL

NUMBER

END_OF_CAL_YEAR

NOT NULL

DATE

END_OF_FIS_YEAR

NOT NULL

DATE