| Oracle® Database Sample Schemas 10g Release 2 (10.2) Part Number B14198-01 |
|
|
View PDF |
| Oracle® Database Sample Schemas 10g Release 2 (10.2) Part Number B14198-01 |
|
|
View PDF |
This chapter describes the scripts used to generate the Oracle Database Sample Schemas. It contains the following sections:
Sample Schemas script directories are located in $ORACLE_HOME/demo/schema. You need to install the companion CD to populate the directories with the Sample Schema scripts. Each schema has two primary scripts:
The xx_main.sql script, here 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.
The script xx_drop.sql, where xx is the schema abbreviation, removes all objects from a particular schema.
Sample Schemas script directories are located in $ORACLE_HOME/demo/schema.
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.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 upon installation.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 above mentioned 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
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.
Table 4-1 Human Resources (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 the HR schema and all its objects |
hr_idx.sql |
Creates indexes on the HR tables |
hr_main.sql |
Main script for the HR schema; calls other scripts |
hr_popul.sql |
Populates the objects |
List of HR 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
Table COUNTRIES Name Null? Type ----------------------------------------- -------- ---------------------------- COUNTRY_ID NOT NULL CHAR(2) COUNTRY_NAME VARCHAR2(40) REGION_ID NUMBER Table DEPARTMENTS Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) Table EMPLOYEES Name Null? Type ----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) 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 JOBS Name Null? Type ----------------------------------------- -------- ---------------------------- JOB_ID NOT NULL VARCHAR2(10) JOB_TITLE NOT NULL VARCHAR2(35) MIN_SALARY NUMBER(6) MAX_SALARY NUMBER(6) Table JOB_HISTORY 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 LOCATIONS 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 REGIONS Name Null? Type ----------------------------------------- -------- ---------------------------- REGION_ID NOT NULL NUMBER REGION_NAME VARCHAR2(25)
This section lists the names of the scripts that create the Order Entry (OE) schema and describes the objects in the schema. Table 4-2 lists the OE scripts in alphabetical order.
Table 4-2 Order Entry (OE) Schema Scripts
| Script Name | Description |
|---|---|
oc_comnt.sql |
Adds comments to the online catalog (OC) subschema wherever possible |
oc_cre.sql |
Creates the OC subschema |
oc_drop.sql |
Drops the OC subschema |
oc_main.sql |
Main script for the OC subschema |
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 the OE schema 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 |
Note:
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.List of OE Objects
FUNCTION GET_PHONE_NUMBER_F 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 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$$ SEQUENCE ORDERS_SEQ SYNONYM COUNTRIES DEPARTMENTS EMPLOYEES JOBS JOB_HISTORY LOCATIONS TABLE CATEGORIES_TAB CUSTOMERS INVENTORIES ORDERS ORDER_ITEMS PRODUCT_DESCRIPTIONS PRODUCT_INFORMATION PRODUCT_REF_LIST_NESTEDTAB PROMOTIONS PURCHASEORDER STYLESHEET_TAB SUBCATEGORY_REF_LIST_NESTEDTAB WAREHOUSES TRIGGER INSERT_ORD_LINE ORDERS_ITEMS_TRG ORDERS_TRG PURCHASEORDER$xd TYPE CATALOG_TYP CATALOG_TYP CATEGORY_TYP CATEGORY_TYP COMPOSITE_CATEGORY_TYP COMPOSITE_CATEGORY_TYP CORPORATE_CUSTOMER_TYP CUSTOMER_TYP CUST_ADDRESS_TYP INVENTORY_LIST_TYP INVENTORY_TYP LEAF_CATEGORY_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
Table CATEGORIES_TAB Name Null? Type ----------------------------------------- -------- ---------------------------- CATEGORY_NAME VARCHAR2(50) CATEGORY_DESCRIPTION VARCHAR2(1000) CATEGORY_ID NOT NULL NUMBER(2) PARENT_CATEGORY_ID NUMBER(2) Table CUSTOMERS 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 INVENTORIES Name Null? Type ----------------------------------------- -------- ---------------------------- PRODUCT_ID NOT NULL NUMBER(6) WAREHOUSE_ID NOT NULL NUMBER(3) QUANTITY_ON_HAND NOT NULL NUMBER(8) Table ORDERS 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 ORDER_ITEMS 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 PRODUCT_DESCRIPTIONS 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 PRODUCT_INFORMATION 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) PRODUCT_REF_LIST_NESTEDTAB Name Null? Type ----------------------------------------- -------- ---------------------------- COLUMN_VALUE NUMBER(6) Table PROMOTIONS Name Null? Type ----------------------------------------- -------- ---------------------------- PROMO_ID NOT NULL NUMBER(6) PROMO_NAME VARCHAR2(20) Table PURCHASEORDER Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE of SYS.XMLTYPE(XMLSchema http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd Element "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T" Table STYLESHEET_TAB Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER STYLESHEET XMLTYPE Table SUBCATEGORY_REF_LIST_NESTEDTAB Name Null? Type ----------------------------------------- -------- ---------------------------- COLUMN_VALUE REF OF CATEGORY_TYP Table WAREHOUSES 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
This section lists the names of the scripts that create the Product Media (PM) schema and describes the objects in the schema. Table 4-3 lists the OE scripts in alphabetical order.
Table 4-3 Product Media (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 the PM schema 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 the PM schema that calls other scripts |
Note:
The SQL*Loader data filepm_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.List of PM 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 TEXTDOCS_NESTEDTAB TYPE ADHEADER_TYP TEXTDOC_TAB TEXTDOC_TYP
PM Table Descriptions
Table ONLINE_MEDIA Name Null? Type ----------------------------------------- -------- ---------------------------- PRODUCT_ID NOT NULL NUMBER(6) PRODUCT_PHOTO ORDSYS.ORDIMAGE PRODUCT_PHOTO_SIGNATURE ORDSYS.ORDIMAGESIGNATURE PRODUCT_THUMBNAIL ORDSYS.ORDIMAGE PRODUCT_VIDEO ORDSYS.ORDVIDEO PRODUCT_AUDIO ORDSYS.ORDAUDIO PRODUCT_TEXT CLOB PRODUCT_TESTIMONIALS ORDSYS.ORDDOC Table PRINT_MEDIA 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 Table TEXTDOCS_NESTEDTAB Name Null? Type ----------------------------------------- -------- ---------------------------- DOCUMENT_TYP VARCHAR2(32) FORMATTED_DOC BLOB
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-4 lists the IX scripts in alphabetical order.
Table 4-4 Information Exchange (IX) Schema Scripts
| Script Name | Description |
|---|---|
cix_v3.sql |
Creates the IX schema objects |
dix_v3.sql |
Drops the IX schema objects |
ix_main.sql |
Main script for the IX schema calls other scripts |
vix_v3.sql |
Enables, disables, and verifies IX objects |
List of IX 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 AQ$_ORDERS_QUEUETABLE_G AQ$_ORDERS_QUEUETABLE_H AQ$_ORDERS_QUEUETABLE_I AQ$_ORDERS_QUEUETABLE_S AQ$_ORDERS_QUEUETABLE_T AQ$_STREAMS_QUEUE_TABLE_G AQ$_STREAMS_QUEUE_TABLE_H AQ$_STREAMS_QUEUE_TABLE_I AQ$_STREAMS_QUEUE_TABLE_S AQ$_STREAMS_QUEUE_TABLE_T ORDERS_QUEUETABLE STREAMS_QUEUE_TABLE SYS_IOT_OVER_45936 SYS_IOT_OVER_45953 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
Table AQ$_ORDERS_QUEUETABLE_G Name Null? Type ----------------------------------------- -------- ---------------------------- MSGID NOT NULL RAW(16) SUBSCRIBER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) ADDRESS# NOT NULL NUMBER SIGN SYS.AQ$_SIG_PROP DBS_SIGN SYS.AQ$_SIG_PROP Table AQ$_ORDERS_QUEUETABLE_H Name Null? Type ------------------------------------ -------- ---------------------------- MSGID NOT NULL RAW(16) SUBSCRIBER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) ADDRESS# NOT NULL NUMBER DEQUEUE_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE TRANSACTION_ID VARCHAR2(30) DEQUEUE_USER VARCHAR2(30) PROPAGATED_MSGID RAW(16) RETRY_COUNT NUMBER HINT ROWID SPARE RAW(16) Table AQ$_ORDERS_QUEUETABLE_I Name Null? Type ------------------------------------ -------- ---------------------------- SUBSCRIBER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) QUEUE# NOT NULL NUMBER MSG_ENQ_TIME NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE MSG_STEP_NO NOT NULL NUMBER MSG_CHAIN_NO NOT NULL NUMBER MSG_LOCAL_ORDER_NO NOT NULL NUMBER MSGID NOT NULL RAW(16) HINT ROWID SPARE RAW(16) Table AQ$_ORDERS_QUEUETABLE_S Name Null? Type ----------------------------------------- -------- ---------------------------- SUBSCRIBER_ID NOT NULL NUMBER QUEUE_NAME NOT NULL VARCHAR2(30) NAME VARCHAR2(30) ADDRESS VARCHAR2(1024) PROTOCOL NUMBER SUBSCRIBER_TYPE NUMBER RULE_NAME VARCHAR2(30) TRANS_NAME VARCHAR2(61) RULESET_NAME VARCHAR2(65) NEGATIVE_RULESET_NAME VARCHAR2(65) Table AQ$_ORDERS_QUEUETABLE_T Name Null? Type ------------------------------------ -------- ---------------------------- NEXT_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE TXN_ID NOT NULL VARCHAR2(30) MSGID NOT NULL RAW(16) ACTION NUMBER Table AQ$_STREAMS_QUEUE_TABLE_G Name Null? Type ----------------------------------------- -------- ---------------------------- MSGID NOT NULL RAW(16) SUBSCRIBER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) ADDRESS# NOT NULL NUMBER SIGN SYS.AQ$_SIG_PROP DBS_SIGN SYS.AQ$_SIG_PROP Table AQ$_STREAMS_QUEUE_TABLE_H Name Null? Type ------------------------------------ -------- ---------------------------- MSGID NOT NULL RAW(16) SUBSCRIBER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) ADDRESS# NOT NULL NUMBER DEQUEUE_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE TRANSACTION_ID VARCHAR2(30) DEQUEUE_USER VARCHAR2(30) PROPAGATED_MSGID RAW(16) RETRY_COUNT NUMBER HINT ROWID SPARE RAW(16) Table AQ$_STREAMS_QUEUE_TABLE_I Name Null? Type ------------------------------------ -------- ---------------------------- SUBSCRIBER# NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) QUEUE# NOT NULL NUMBER MSG_ENQ_TID NOT NULL VARCHAR2(30) SENDER# NOT NULL NUMBER TXN_STEP# NOT NULL NUMBER MSG_ENQ_TIME NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE MSG_STEP_NO NOT NULL NUMBER MSG_CHAIN_NO NOT NULL NUMBER MSG_LOCAL_ORDER_NO NOT NULL NUMBER MSGID NOT NULL RAW(16) HINT ROWID SPARE RAW(16) Table AQ$_STREAMS_QUEUE_TABLE_S Name Null? Type ----------------------------------------- -------- ---------------------------- SUBSCRIBER_ID NOT NULL NUMBER QUEUE_NAME NOT NULL VARCHAR2(30) NAME VARCHAR2(30) ADDRESS VARCHAR2(1024) PROTOCOL NUMBER SUBSCRIBER_TYPE NUMBER RULE_NAME VARCHAR2(30) TRANS_NAME VARCHAR2(61) RULESET_NAME VARCHAR2(65) NEGATIVE_RULESET_NAME VARCHAR2(65) Table AQ$_STREAMS_QUEUE_TABLE_T Name Null? Type ------------------------------------ -------- ---------------------------- NEXT_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE TXN_ID NOT NULL VARCHAR2(30) MSGID NOT NULL RAW(16) ACTION NUMBER Table ORDERS_QUEUETABLE Name Null? Type ------------------------------------ -------- ---------------------------- Q_NAME VARCHAR2(30) MSGID NOT NULL RAW(16) CORRID VARCHAR2(128) PRIORITY NUMBER STATE NUMBER DELAY TIMESTAMP(6) WITH LOCAL TIME ZONE EXPIRATION NUMBER TIME_MANAGER_INFO TIMESTAMP(6) WITH LOCAL TIME ZONE LOCAL_ORDER_NO NUMBER CHAIN_NO NUMBER CSCN NUMBER DSCN NUMBER ENQ_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE ENQ_UID VARCHAR2(30) ENQ_TID VARCHAR2(30) DEQ_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE DEQ_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 STREAMS_QUEUE_TABLE Name Null? Type ------------------------------------ -------- ---------------------------- Q_NAME VARCHAR2(30) MSGID NOT NULL RAW(16) CORRID VARCHAR2(128) PRIORITY NUMBER STATE NUMBER DELAY TIMESTAMP(6) WITH LOCAL TIME ZONE EXPIRATION NUMBER TIME_MANAGER_INFO TIMESTAMP(6) WITH LOCAL TIME ZONE LOCAL_ORDER_NO NUMBER CHAIN_NO NUMBER CSCN NUMBER DSCN NUMBER ENQ_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE ENQ_UID VARCHAR2(30) ENQ_TID VARCHAR2(30) DEQ_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE DEQ_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_PROP SYS.ANYDATA USER_DATA SYS.ANYDATA
This section lists the names of the scripts that create the Sales History (SH) schema and describes the objects in the schema. Table 4-5 lists the SH scripts in alphabetical order.
Table 4-5 Sales History (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 the SH schema and all its objects |
sh_idx.sql |
Creates indexes on tables in the schema |
sh_main.sql |
Main script for the SH schema 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 |
List of SH 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 CAL_MONTH_SALES_MV CHANNELS COSTS COUNTRIES CUSTOMERS DR$SUP_TEXT_IDX$I DR$SUP_TEXT_IDX$K DR$SUP_TEXT_IDX$N DR$SUP_TEXT_IDX$R FWEEK_PSCAT_SALES_MV MVIEW$_EXCEPTIONS PRODUCTS PROMOTIONS SALES SALES_TRANSACTIONS_EXT SUPPLEMENTARY_DEMOGRAPHICS TIMES TABLE PARTITION COSTS SALES VIEW PROFITS
SH Table Descriptions
Table CAL_MONTH_SALES_MV Name Null? Type ----------------------------------------- -------- ---------------------------- CALENDAR_MONTH_DESC NOT NULL VARCHAR2(8) DOLLARS NUMBER Table CHANNELS 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 COSTS Name Null? Type ----------------------------------------- -------- ---------------------------- PROD_ID NOT NULL NUMBER TIME_ID 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 COUNTRIES 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 CUSTOMERS 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 DR_$SUP_TEXT_IDX$I Name Null? Type ----------------------------------------- -------- ---------------------------- TOKEN_TEXT NOT NULL VARCHAR2(64) TOKEN_TYPE NOT NULL NUMBER(3) TOKEN_FIRST NOT NULL NUMBER(10) TOKEN_LAST NOT NULL NUMBER(10) TOKEN_COUNT NOT NULL NUMBER(10) TOKEN_INFO BLOB Table DR$SUP_TEXT_IDX$K Name Null? Type ----------------------------------------- -------- ---------------------------- DOCID NUMBER(38) TEXTKEY NOT NULL ROWID Table DR$SUP_TEXT_IDX$N Name Null? Type ----------------------------------------- -------- ---------------------------- NLT_DOCID NOT NULL NUMBER(38) NLT_MARK NOT NULL CHAR(1) Table DR$SUP_TEXT_IDX$R Name Null? Type ----------------------------------------- -------- ---------------------------- ROW_NO NUMBER(3) DATA BLOB Table FWEEK_PSCAT_SALES_MV Name Null? Type ----------------------------------------- -------- ---------------------------- WEEK_ENDING_DAY NOT NULL DATE PROD_SUBCATEGORY NOT NULL VARCHAR2(50) DOLLARS NUMBER CHANNEL_ID NOT NULL NUMBER PROMO_ID NOT NULL NUMBER Table MVIEW$_EXCEPTIONS Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) DIMENSION_NAME NOT NULL VARCHAR2(30) RELATIONSHIP NOT NULL VARCHAR2(11) BAD_ROWID NOT NULL ROWID Table PRODUCTS 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) PROD_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) PROD_PACK_SIZE NOT NULL VARCHAR2(30) SUPPLIER_ID NOT NULL NUMBER(6) PROD_STATUS NOT NULL VARCHAR2(20) PROD_LIST_PRICE NOT NULL NUMBER(8,2) PROD_MIN_PRICE NOT NULL NUMBER(8,2) PROD_TOTAL NOT NULL VARCHAR2(13) PROD_TOTAL_ID NOT NULL NUMBER PROD_SRC_ID NUMBER PROD_EFF_FROM DATE PROD_EFF_TO DATE PROD_VALID VARCHAR2(1) Table PROMOTIONS 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) PROMO_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 SALES 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 SALES_TRANSACTIONS_EXT Name Null? Type ----------------------------------------- -------- ---------------------------- PROD_ID NUMBER CUST_ID NUMBER TIME_ID DATE CHANNEL_ID NUMBER PROMO_ID NUMBER QUANTITY_SOLD NUMBER AMOUNT_SOLD NUMBER(10,2) UNIT_COST NUMBER(10,2) UNIT_PRICE NUMBER(10,2) Table SUPPLEMENTARY_DEMOGRAPHICS Name Null? Type ----------------------------------------- -------- ---------------------------- CUST_ID NOT NULL NUMBER EDUCATION VARCHAR2(21) OCCUPATION VARCHAR2(21) HOUSEHOLD_SIZE VARCHAR2(21) YRS_RESIDENCE NUMBER AFFINITY_CARD NUMBER(10) BULK_PACK_DISKETTES NUMBER(10) FLAT_PANEL_MONITOR NUMBER(10) HOME_THEATER_PACKAGE NUMBER(10) BOOKKEEPING_APPLICATION NUMBER(10) PRINTER_SUPPLIES NUMBER(10) Y_BOX_GAMES NUMBER(10) OS_DOC_SET_KANJI NUMBER(10) COMMENTS VARCHAR2(4000) Table TIMES 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) CALENDAR_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
