Oracle® Database

Sample Schemas

10g Release 2 (10.2)

B14198-01

June 2005

Oracle Database Sample Schemas, 10g Release 2 (10.2)

B14198-01

Copyright © 2001, 2005, Oracle. All rights reserved.

Primary Author:  Christian Bauwens

Contributing Authors:  David Austin, Mark Drake, Nancy Greenberg, Alexander Hunold, Diana Lorentz, Nagavalli Pataballa, Den Raphaely, Sandhya Sridharan

The Programs (which include both the software and documentation) contain proprietary information; they are provided under a license agreement containing restrictions on use and disclosure and are also protected by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly, or decompilation of the Programs, except to the extent required to obtain interoperability with other independently created software or as specified by law, is prohibited.

The information contained in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. This document is not warranted to be error-free. Except as may be expressly permitted in your license agreement for these Programs, no part of these Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose.

If the Programs are delivered to the United States Government or anyone licensing or using the Programs on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial Computer Software—Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City, CA 94065

The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and we disclaim liability for any damages caused by such use of the Programs.

Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

The Programs may provide links to Web sites and access to content, products, and services from third parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites. You bear all risks associated with the use of such content. If you choose to purchase any products or services from a third party, the relationship is directly between you and the third party. Oracle is not responsible for: (a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the third party, including delivery of products or services and warranty obligations related to purchased products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from dealing with any third party.

Contents

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Customer Benefits of Sample Schemas

About Sample Schemas

Oracle Database Sample Schemas Design Principles

Structure

Related Documents

Conventions

1 Installation

Using the Database Configuration Assistant

Manually Installing Sample Schemas

Schema Dependencies

Installing the HR Schema

Installing the OE Schema and its OC Subschema

Installing the PM Schema

Installing the IX Schema

Installing the SH Schema

Resetting Sample Schemas
2 Rationale

Overall Description

HR

OE

PM

IX

SH

3 Diagrams

Sample Schema Diagrams

4 Sample Schema Scripts and Object Descriptions

About the Scripts

Master Script

mksample.sql

HR Schema OE Schema PM Schema IX Schema SH Schema
Index

List of Tables

4-1 Human Resources (HR) Schema Scripts

4-2 Order Entry (OE) Schema Scripts

4-3 Product Media (PM) Schema Scripts

4-4 Information Exchange (IX) Schema Scripts

4-5 Sales History (SH) Schema Scripts

Send Us Your Comments

Oracle Database Sample Schemas, 10g Release 2 (10.2)

B14198-01

Oracle Corporation welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision.

- Did you find any errors?

- Is the information clearly presented?

- Do you need more information? If so, where?

- Are the examples correct? Do you need more examples?

- What features did you like most about this manual?

If you find any errors or have any other suggestions for improvement, please indicate the title and part number of the documentation and the chapter, section, and page number (if available). You can send comments to us in the following ways:

- Electronic mail: infodev_us@oracle.com

- FAX: (650) 506-7227  Attn: Server Technologies Documentation Manager

- Postal service:

Oracle Corporation

Oracle Server Technologies Documentation

500 Oracle Parkway, Mailstop 4op11

Redwood Shores, CA 94065

U.S.A.

If you would like a reply, please give your name, address, telephone number, and (optionally) your electronic mail address.

If you have problems with the software, please contact your local Oracle Support Services.

Preface

Oracle used the schema SCOTT with its two prominent tables EMP and DEPT for many years. With advances in Oracle Database technology, these tables have become inadequate to show even the most basic features of Oracle Database and other Oracle products. As a result, many other schemas have been created over the years to suit the needs of product documentation, courseware, software development, and application demos.

This preface contains these topics:

- Intended Audience

- Documentation Accessibility

- Customer Benefits of Sample Schemas

- About Sample Schemas

- Oracle Database Sample Schemas Design Principles

- Structure

- Related Documents

- Conventions

Intended Audience

Sample Schemas is for all users of the seed database, which is installed when you install the Oracle Database.

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Accessibility standards will continue to evolve over time, and Oracle is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For more information, visit the Oracle Accessibility Program Web site at

http://www.oracle.com/accessibility/

Accessibility of Code Examples in Documentation

Screen readers may not always correctly read the code examples in this document. The conventions for writing code require that closing braces should appear on an otherwise empty line; however, some screen readers may not always read a line of text that consists solely of a bracket or brace.

Accessibility of Links to External Web Sites in Documentation

This documentation may contain links to Web sites of other companies or organizations that Oracle does not own or control. Oracle neither evaluates nor makes any representations regarding the accessibility of these Web sites.

TTY Access to Oracle Support Services

Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services within the United States of America 24 hours a day, seven days a week. For TTY support, call 800.446.2398.

Customer Benefits of Sample Schemas

Benefits of Sample Schemas are as follows:

- Continuity of context. When encountering the same set of tables everywhere, users, students, and developers can spend less time becoming familiar with the schema and more time understanding or explaining the technical concepts.

- Usability. Customers can use these schemas in the seed database to run examples that are shown in Oracle documentation and training materials. This first-hand access to examples facilitates both conceptual understanding and application development.

- Quality. Through central maintenance and testing of both the creation scripts that build Sample Schemas and the examples that run against the schemas, the quality of Oracle documentation and training materials is enhanced.

About Sample Schemas

The Oracle Database Sample Schemas provide a common platform for examples in each release of the Oracle Database. All Oracle Database documentation and training materials are being converted to Sample Schemas environment as those materials are updated.

The Oracle Database Sample Schemas are a set of interlinked schemas. This set of schemas provides a layered approach to complexity:

- A simple schema Human Resources (HR) is useful for introducing basic topics. An extension to this schema supports Oracle Internet Directory demos.

- A second schema Order Entry (OE) is useful for dealing with matters of intermediate complexity. Many data types are available in this schema, including nonscalar data types.

- The Online Catalog (OC) subschema is a collection of object-relational database objects built inside the OE schema.

- The Product Media (PM) schema is dedicated to multimedia data types.

- A set of schemas gathered under the main schema name Information Exchange (IX ) can demonstrate Oracle Advanced Queuing capabilities.

- The Sales History (SH) schema is designed to allow for demos with large amounts of data. An extension to this schema provides support for advanced analytic processing.

Oracle Database Sample Schemas Design Principles

Sample Schemas have been created and enhanced with the following design principles in mind:

- Simplicity and ease of use. The HR and OE schemas are intentionally simple. They will not become overly complex by the addition of features. Rather, they are intended to provide a graduated path from the simple to intermediate levels of database use.

- Relevance for typical users. The base schemas and the extensions bring to the foreground the functionality that customers typically use. Only the most commonly used database objects are built automatically in the schemas. The entire set of schemas provides a foundation upon which one can expand to illustrate additional functionality.

- Extensibility. Sample Schemas provide a logical and physical foundation for adding objects to demonstrate functionality beyond the fundamental scope.

- Relevance. Sample Schemas are designed to be applicable to e-business and other significant industry trends (for example, XML). When this goal conflicts with the goal of simplicity, schema extensions are used to showcase the trends in focus.

Structure

This document contains the following chapters:

Chapter 1, "Installation"

This chapter describes how to install the Oracle Database Sample Schemas.

Chapter 2, "Rationale"

This chapter describes the fictitious company on which Sample Schemas are based.

Chapter 3, "Diagrams"

This chapter contains diagrams of Sample Schemas.

Chapter 4, "Sample Schema Scripts and Object Descriptions"

This chapter lists the Sample Schema creation scripts and describes the Sample Schema objects.

Related Documents

Printed documentation is available for sale in the Oracle Store at

http://oraclestore.oracle.com/

To download free release notes, installation documentation, white papers, or other collateral, please visit the Oracle Technology Network (OTN). You must register online before using OTN registration is free and can be done at

http://www.oracle.com/technology/membership/index.html

If you already have a username and password for OTN, then you can go directly to the documentation section of the OTN Web site at

http://www.oracle.com/technology/documentation/index.html

Conventions

This section describes the conventions used in the text and code examples of this documentation set. It describes:

- Conventions in Text

- Conventions in Code Examples

Conventions in Text

We use various conventions in text to help you more quickly identify special terms. The following table describes those conventions and provides examples of their use.

Convention

Meaning

Example

Bold

Bold typeface indicates terms that are defined in the text or terms that appear in a glossary, or both.

When you specify this clause, you create an index-organized table.

Italics

Italic typeface indicates book titles or emphasis.

Oracle Database Concepts
Ensure that the recovery catalog and target database do not reside on the same disk.

UPPERCASE monospace (fixed-width) font

Uppercase monospace typeface indicates elements supplied by the system. Such elements include parameters, privileges, data types, RMAN keywords, SQL keywords, SQL*Plus or utility commands, packages and methods, as well as system-supplied column names, database objects and structures, usernames, and roles.

You can specify this clause only for a NUMBER column.
You can back up the database by using the BACKUP command.

Query the TABLE_NAME column in the USER_TABLES data dictionary view.

Use the DBMS_STATS.GENERATE_STATS procedure.

lowercase monospace (fixed-width) font

Lowercase monospace typeface indicates executables, filenames, directory names, and sample user-supplied elements. Such elements include computer and database names, net service names, and connect identifiers, as well as user-supplied database objects and structures, column names, packages and classes, usernames and roles, program units, and parameter values.
Note: Some programmatic elements use a mixture of UPPERCASE and lowercase. Enter these elements as shown.

Enter sqlplus to start SQL*Plus.
The password is specified in the orapwd file.

Back up the datafiles and control files in the /disk1/oracle/dbs directory.

The department_id, department_name, and location_id columns are in the hr.departments table.

Set the QUERY_REWRITE_ENABLED initialization parameter to true.

Connect as oe user.

The JRepUtil class implements these methods.

lowercase italic monospace (fixed-width) font

Lowercase italic monospace font represents placeholders or variables.

You can specify the parallel_clause.
Run old_release.SQL where old_release refers to the release you installed prior to upgrading.

Conventions in Code Examples

Code examples illustrate SQL, PL/SQL, SQL*Plus, or other command-line statements. They are displayed in a monospace (fixed-width) font and separated from normal text as shown in this example:

SELECT username FROM dba_users WHERE username = 'MIGRATE';

The following table describes typographic conventions used in code examples and provides examples of their use.

Convention

Meaning

Example

[ ]

Brackets enclose one or more optional items. Do not enter the brackets.

DECIMAL (digits [ , precision ])

{ }

Braces enclose two or more items, one of which is required. Do not enter the braces.

{ENABLE | DISABLE}

|

A vertical bar represents a choice of two or more options within brackets or braces. Enter one of the options. Do not enter the vertical bar.

{ENABLE | DISABLE}
[COMPRESS | NOCOMPRESS]

...

Horizontal ellipsis points indicate either:

- That we have omitted parts of the code that are not directly related to the example

- That you can repeat a portion of the code

CREATE TABLE ... AS subquery;

SELECT col1, col2, ... , coln FROM employees;

.
.
.

Vertical ellipsis points indicate that we have omitted several lines of code not directly related to the example.

SQL> SELECT NAME FROM V$DATAFILE;
NAME
------------------------------------
/fsl/dbs/tbs_01.dbf
/fs1/dbs/tbs_02.dbf
.
.
.
/fsl/dbs/tbs_09.dbf
9 rows selected.

Other notation

You must enter symbols other than brackets, braces, vertical bars, and ellipsis points as shown.

acctbal NUMBER(11,2);
acct    CONSTANT NUMBER(4) := 3;

Italics

Italicized text indicates placeholders or variables for which you must supply particular values.

CONNECT SYSTEM/system_password
DB_NAME = database_name

UPPERCASE

Uppercase typeface indicates elements supplied by the system. We show these terms in uppercase in order to distinguish them from terms you define. Unless terms appear in brackets, enter them in the order and with the spelling shown. However, because these terms are not case sensitive, you can enter them in lowercase.

SELECT last_name, employee_id FROM employees;
SELECT * FROM USER_TABLES;
DROP TABLE hr.employees;

lowercase

Lowercase typeface indicates programmatic elements that you supply. For example, lowercase indicates names of tables, columns, or files.
Note: Some programmatic elements use a mixture of UPPERCASE and lowercase. Enter these elements as shown.

SELECT last_name, employee_id FROM employees;
sqlplus hr/hr
CREATE USER mjones IDENTIFIED BY ty3MU9;

1 Installation

During a complete installation of Oracle Database, Sample Schemas can be installed automatically with the seed database. If the seed database is removed from your system, then you will need to reinstall Sample Schemas before you can duplicate the examples you find in Oracle documentation and training materials.

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

- Using the Database Configuration Assistant

- Manually Installing Sample Schemas

- Resetting Sample Schemas

Caution:

By installing any of the Oracle Database Sample Schemas, you will destroy any previously installed schemas that use any of the following user names:

- HR

- OE

- PM

- SH

- IX

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 Oracle Database 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

When you install Oracle with the Oracle Universal Installer, the sample schemas are installed by default when you select the Basic Installation option. If you have installed your database with the Advanced Option and did not choose to install the sample schemas, you can install the Sample Schemas with the Database Configuration Assistant (DBCA). When you run DBCA, selecting the Sample Schemas option installs all five schemas (HR, OE, PM, IX, SH) in your database.

At the end of the installation process, a window displays the accounts that have been created and their lock status. All Sample Schemas are locked by default. You can unlock the accounts at this point in the installation process. Alternatively, after the installation is complete, you can unlock the schemas with an ALTER USER ... ACCOUNT UNLOCK statement.

The sample schemas available to you depend on the edition of Oracle that you have installed and its configuration. Refer to the following table:

Schema

Oracle Database Personal edition

Oracle Database Standard edition

Oracle Database Enterprise edition

HR

OK

OK

OK

OE

OK

OK

OK

PM

OK

OK

OK

IX

OK

OK

OK

SH

Not available

Not available

Needs Partitioning Option installed

Manually Installing Sample Schemas

You can also create Sample Schemas manually by running SQL scripts, rather than using DBCA. The scripts are included in the companion directory on the installation medium.

Schema Dependencies

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

Use this sequence to create the schemas:

1. Create the HR schema.

2. Create the OE schema: The HR schema is already present, and you must know the password for the HR schema to grant HR object privileges to OE. Some HR tables are visible to the OE user by using 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 later 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 later using the Database Configuration Assistant.

4. Create the IX schema: The information exchange schema IX is based on order entry data in OE. Again, foreign key relationships require that the OE schema already be present when the IX schema is created. You need to know the password for OE to grant to IX the right to establish and use the foreign keys.

5. Create the SH schema. The SH schema logically depends on the OE schema, though you can create this schema without creating the other four schemas.

Installing the HR Schema

All scripts necessary to create Human Resource (HR) schema reside in $ORACLE_HOME/demo/schema/human_resources.

You need to call only one script, hr_main.sql, to create all the 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. Removes any previously installed HR schema

3. Creates the user HR and grants the necessary privileges

4. Connects as HR

5. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-1.

A pair of optional scripts, hr_dn_c.sql and hr_dn_d.sql, is provided as a schema extension. To prepare the HR schema 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 the HR schema, then use the hr_dn_d.sql script to undo the effects of the hr_dn_c.sql script.

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

Installing the OE Schema and its OC Subschema

All scripts necessary to create the Order Entry (OE) schema and its Online Catalog (OC) subschema reside in $ORACLE_HOME/demo/schema/order_entry.

You need to call only one script, oe_main.sql, to create all the 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. Removes any previously installed OE schema

3. Creates the user OE and grants the necessary privileges

4. Connects as OE

5. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-2, respectively.

The oe_drop.sql and oc_drop.sql scripts are used to drop the OE schema and OC subschema.

Installing the PM Schema

All files necessary to create Product Media (PM) schema reside in $ORACLE_HOME/demo/schema/product_media.

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

1. Prompts for passwords and tablespace names used within the scripts, as well as datafile and log file directories

2. Removes any previously installed PM schema

3. Creates the user PM and grants the necessary privileges

4. Connects as PM

5. Calls the following scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-3 .

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

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.

Installing the IX Schema

To install the Information Exchange (IX) schema, you need to call only one script, ix_main.sql, to create all the objects and load the data. Running ix_main.sql accomplishes the following tasks:

1. Prompts for passwords and tablespace names used within the scripts

2. Removes any previously installed IX schema

3. Creates the user IX and grants the necessary privileges

4. Connects as IX

5. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-4.

The ix_drop.sql script is used for dropping the IX schema.

Installing the SH Schema

All files necessary to create the Sales History (SH) schema reside in $ORACLE_HOME/demo/schema/sales_history.

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

1. Prompts for passwords and tablespace names used within the scripts, as well as datafile and log file directories

2. Removes any previously installed SH schema

3. Creates the user SH and grants the necessary privileges

4. Connects as SH

5. Calls the scripts that create and populate the schema objects

For a complete listing of the scripts and their functions, refer to Table 4-5.

Note:

The dimension tables PROMOTIONS, CUSTOMERS, PRODUCTS and the fact table SALES are loaded by SQL*Loader, after which directory paths are created inside the database to point to the load and log file locations. This allows the loading of the COSTS table by using the external table sales_transactions_ext.

A pair of optional scripts, sh_olp_c.sql and sh_olp_d.sql, is provided as a schema extension. To prepare the SH schema for use with the advanced analytical 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, then 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 Sample Schemas

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

@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/

The mksample script expects 11 parameters. Provide the password for SYSTEM and SYS, and for the HR, OE, PM, IX, SH, and BI schemas. Specify a temporary and a default tablespace, and make sure to end the name of the log file directory with a trailing slash.

The mksample script produces several log files:

- mkverify.log is the Sample Schema creation log file.

- hr_main.log is the HR schema creation log file.

- oe_oc_main.log is the OE schema creation log file.

- pm_main.log is the PM schema creation log file.

- pm_p_lob.log is the SQL*Loader log file for PM.PRINT_MEDIA.

- ix_main.log is the IX schema creation log file.

- sh_main.log is the SH schema creation log file.

- cust.log is the SQL*Loader log file for SH.CUSTOMERS.

- prod.log is the SQL*Loader log file for SH.PRODUCTS.

- promo.log is the SQL*Loader log file for SH.PROMOTIONS.

- sales.log is the SQL*Loader log file for SH.SALES.

- sales_ext.log is the external table log file for SH.COSTS.

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 their objects.

In some cases, complex interobject relationships in the OE or IX schemas prevent the DROP USER ... CASCADE operations from completing normally. To correct these rare cases, use one of the following procedures:

For the OC catalog subschema of the OE schema:

1. Connect as the user OE.

2. Run the oc_drop.sql. script

3. Connect as SYSTEM.

4. Ensure that no user is connected as OE:

SELECT username FROM v$session;

5. Drop the user:

DROP USER oe CASCADE;

For the IX schemas:

1. Connect as SYSTEM.

2. Ensure that no user is connected as an IX user:

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

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

2 Rationale

The Oracle Database Sample Schemas are based on a fictitious company that sells goods through various channels. This chapter describes the fictitious company and contains the following sections:

- Overall Description

- HR

- OE

- PM

- IX

- SH

Overall Description

The sample company portrayed by the Oracle Database Sample Schemas operates worldwide to fill orders for several products. The company has several divisions:

- The Human Resources division tracks information on the employees and the facilities.

- The Order Entry division tracks product inventories and sales of company's products through various channels.

- The Product Media division maintains descriptions and detailed information on each product sold by the company.

- The Information Exchange division manages shipping through B2B applications.

- The Sales division tracks business statistics to facilitate business decisions.

Each of these divisions is represented by a schema.

HR

In the Human Resource (HR) records, each employee has an identification number, e-mail address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary.

The company also tracks information about jobs within the organization. Each job has an identification code, job title, and a minimum and maximum salary range for the job. Some employees have been with the company for a long time and have held different positions within the company. When an employee resigns, the duration the employee was working, the job identification number, and the department are recorded.

The sample company is regionally diverse, so it tracks the locations of its warehouses and departments. Each employee is assigned to a department, and each department is identified either by a unique department number or a short name. Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code.

In places where the departments and warehouses are located, the company records details such as, the country name, currency symbol, currency name, and the region where the country resides geographically.

OE

The company sells several products, such as computer hardware and software, music, clothing, and tools. The company maintains information about these products, such as, product identification numbers, the category into which the product falls, order entry (OE), the weight group (for shipping purposes), the warranty period if applicable, the supplier, the availability status of the product, a list price, a minimum price at which a product will be sold, and a URL address for manufacturer information. Inventory information is also recorded for all products, including the warehouse where the product is available and the quantity on hand. Because products are sold worldwide, the company maintains the names of the products and their descriptions in several languages.

The company maintains warehouses in several locations to fulfill customer needs. Each warehouse has a warehouse identification number, name, facility description, and location identification number.

Customer information is also tracked. Each customer has an identification number. Customer records include customer name, street name, city or province, country, phone numbers (up to five phone numbers for each customer), and postal code. Some customers place orders through the Internet, so e-mail addresses are also recorded. Because of language differences among customers, the company records the native language and territory of each customer.

The company places a credit limit on its customers, to limit the amount of products they can purchase at one time. Some customers have an account manager, and this information is also recorded.

When a customer places an order, the company tracks the date of the order, how the order was placed, the current status of the order, shipping mode, total amount of the order, and the sales representative who helped place the order. The sales representative may or may not be the same person as the account manager for a customer. If an order is placed over the Internet, no sales representative is recorded. In addition to the order information, the company also tracks the number of items ordered, the unit price, and the products ordered.

The OE schema also contains XML purchase order documents. The XML documents are stored in the Oracle XML DB Repository after validation against the registered XML schema purchaseorder.xsd. You can access these documents in various ways, such as querying the PURCHASEORDER object-relational table using SQL, querying public views RESOURCE_VIEW and PATH_VIEW, and querying the repository using XPath expressions.

The purchase order XML documents are located in the Oracle XML DB Repository folder $ORACLE_HOME/rdbms/demo/order_entry/2002/month, where month is a three-letter month abbreviation (for example, Jan, Feb, Mar, and so on).

OC Description

The Online Catalog (OC) subschema of the OE schema addresses an online catalog merchandising scenario. The same customers and products are used in OC as in the OE schema proper, but the OC subschema organizes the products into a hierarchy of parent categories and subcategories. This hierarchy corresponds to the arrangement on an e-commerce portal site, where users navigate to specific products by drilling down through increasingly specialized categories of products.

PM

The company stores multimedia and print information about its products in the database. Product Media (PM) schema is used to store such data. Examples of such information are:

- Promotional audio and video clips

- Product images and thumbnails for Web publishing

- Press release texts

- Print media advertisements

- Other promotion texts and translations

IX

The company has decided to test the use of messaging to manage its proposed B2B applications. The plan calls for a small test that will allow a user from outside the firewall to place an order and track its status. The order needs to be booked into the main system. Then, depending on the location of the customer, the order is routed to the nearest region for shipping. The (IX) schema stores such information.

Eventually, the company intends to expand beyond its current in-house distribution system to a system that will allow other businesses to provide the shipping. The messages sent must be in a self-contained format. XML is the perfect format for sending messages, and both Advanced Queueing Servlet and Oracle Internet Directory provide the appropriate routing between the queues.

After the orders are either shipped or back ordered, a message must be sent back to the employee concerned to inform about the status of the order and to initiate the billing cycle. It is important that the message be delivered only once and that there be a system for tracking and reviewing messages to facilitate resolution of any discrepancies with the order.

For the purpose of this test application, the company uses a single database server and a single application server. The application provides a mechanism for examining the XML messages, as well as looking at the queues. To demonstrate connectivity from outside the firewall, both the generation of a new order and customer service reporting are performed using queues. The new order application directly enables a queue, while the customer service queries require XML messaging to disable a queue.

SH

The sample company does a high volume of business, so it runs business statistics reports to aid in decision making. Many of these reports are time-based and nonvolatile. That is, they analyze past data trends. The company loads data into its data warehouse regularly to gather statistics for these reports. These reports include annual, quarterly, monthly, and weekly sales figures by product. These reports are stored with the help of Sales History (SH) schema.

The company also runs reports on distribution channels through which its sales are delivered. When the company runs special promotions on its products, it analyzes the impact of the promotions on sales. It also analyzes sales by geographical area.

3 Diagrams

This chapter contains diagrams of Sample Schemas. The first diagram shows the build order and prerequisites of Sample Schemas. The remaining diagrams illustrate the configuration of the various components of each schema.

Sample Schema Diagrams

This diagram shows the build order and prerequisites of Sample Schemas.

The following diagrams illustrate the configuration of the components of each schema.

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

- Master Script

- HR Schema

- OE Schema

- PM Schema

- IX Schema

- SH Schema

About the Scripts

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.

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 upon 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 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

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.

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)

OE Schema

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

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-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 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.

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

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-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

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-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

Index
D  E  H  I  O  P  R  S

D

Database Configuration Assistant

using to install Sample Schemas, 1.1

dependencies among schemas, 1.2.1 DEPT table, Preface

E

EMP table, Preface

H

HR schema

general description, 2.2, 2.2

installing, 1.2.2

I

installation

manual, of Sample Schemas, 1.2

of HR schema, 1.2.2

of IX schema, 1.2.5

of OC subschema, 1.2.3

of OE schema, 1.2.3

of PM schema, 1.2.4

of SH schema, 1.2.6

order of, 1.2.1

using Database Configuration Assistant, 1.1

installation of Sample Schemas, 1 IX schema

general description, 2.5, 2.5

installing, 1.2.5

scripts, 4.6

O

OC subschema

general description, 2.3, 2.3

installing, 1.2.3

OE schema

general description, 2.3, 2.3

installing, 1.2.3

scripts, 4.4

P

PM schema

general description, 2.4, 2.4

installing, 1.2.4

scripts, 4.5

R

resetting the Sample Schemas, 1.3

S

Sample Schema

diagrams, 3

scripts

OE, 4.4

PM, 4.5

QS, 4.6

SH, 4.7

scripts, general information, 4.1 Sample Schemas

design principles, Preface

general description, 2.1

general information, Preface

installing, 1

scripts

master, 4.2

SCOTT schema, Preface SH schema

general description, 2.6

installing, 1.2.6

scripts, 4.7

Oracle Legal Notices

Copyright Notice

Copyright © 1994-2016, Oracle and/or its affiliates. All rights reserved.

License Restrictions Warranty/Consequential Damages Disclaimer

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

Warranty Disclaimer

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

Restricted Rights Notice

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.

Hazardous Applications Notice

This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.

Third-Party Content, Products, and Services Disclaimer

This software or hardware and documentation may provide access to or information about content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services, except as set forth in an applicable agreement between you and Oracle.

Alpha and Beta Draft Documentation Notice

If this document is in preproduction status:

This documentation is in preproduction status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.

Private Alpha and Beta Draft Documentation Notice

If this document is in private preproduction status:

The information contained in this document is for informational sharing purposes only and should be considered in your capacity as a customer advisory board member or pursuant to your beta trial agreement only. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described in this document remains at the sole discretion of Oracle.

This document in any form, software or printed matter, contains proprietary information that is the exclusive property of Oracle. Your access to and use of this confidential material is subject to the terms and conditions of your Oracle Master Agreement, Oracle License and Services Agreement, Oracle PartnerNetwork Agreement, Oracle distribution agreement, or other license agreement which has been executed by you and Oracle and with which you agree to comply. This document and information contained herein may not be disclosed, copied, reproduced, or distributed to anyone outside Oracle without prior written consent of Oracle. This document is not part of your license agreement nor can it be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates.

Documentation Accessibility

For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.

Access to Oracle Support

Oracle customers that have purchased support have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.