Creating the Oracle Sample Schema - (Release 9i and 10g)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Overview
  2. About the Sample Schemas
  3. Schema Dependencies
  4. The EXAMPLE Tablespace
  5. Running the Schema Creation Scripts - (9i)
  6. Running the Schema Creation Scripts - (10g)

Overview

Developer's and DBA's who have worked with Oracle in the past should be familiar with the ever popular SCOTT schema which contained the two tables EMP and DEPT. With all of the advances in Oracle technology (i.e. partitions, dimensions, objects, materialized views, etc.), these tables have become inadequate to show even the most basic features of the 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, and software development and application demos.

There are several ways in which to installed the sample schemas:

  1. When you perform a complete installation of Oracle9i, the Sample Schemas are installed automatically with the seed database. If for some reason the seed database is removed from your system, you will need to reinstall the Sample Schemas.

    The new Oracle9i Sample Schemas serve to provide a common platform for examples in Oracle9i and future releases. The new Oracle9i Sample Schemas are a set of interlinked schemas. This set of schemas is aimed at providing a layered approach to complexity:

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

    • A second schema (Order Entry, OE) for dealing with matters of intermediate complexity. A multitude of datatypes is available in this schema.

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

    • A schema dedicated to multimedia datatypes (Product Media, PM)

    • A set of schemas gathered under the main schema name QS (Queued Shipping, QS) to demonstrate Oracle Advanced Queuing capabilities.

    • A schema designed to allow for demos with larger amounts of data (Sales History, SH). An extension to this schema provides support for advanced analytic processing.
  2. Using DBCA is by far the most intuitive and simple way to install the Sample Schemas. Step 4 of the database creation process lets you configure the Sample Schemas you wish to use in your database.

  3. Manual method, using SQL scripts.

This article provides the steps necessary to manually setup all of the example schemas and objects available with Oracle9i.



About the Sample Schemas

The new Oracle9i Sample Schemas serve to provide a common platform for examples in Oracle9i and future releases. The new Oracle9i Sample Schemas are a set of interlinked schemas. This set of schemas is aimed at providing a layered approach to complexity:



Schema Dependencies

Before going into the schema creation details, let's first talk about the dependencies within the schemas.

NOTE: The following list will not only document the dependencies between the different schemas, but will also show the sequence used to create the schemas.

Also remember that the Sample Schemas are ordered, both in complexity and dependencies, in alphabetical order.

  1. HR : Has no dependencies. This should be the first schema to create.

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

    The OE schema also requires the Oracle Spatial option be installed. This can be done during installation or afterward using the Database Configuration Assistant.

  3. PM : Foreign key relationships require that the OE schema alread exist when the PM schema is created. You will need to know the password for the OE user in order to grant to the PM user, the right to establish and use these foreign keys.

    The PM schema also requires the the database to be enabled for the Java Virtual Machine (Oracle JVM) and interMedia. This can be accomplished during installation or afterward using the Database Configuration Assistant.

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

  5. SH : The SH schema logically depends on the OE schema, although there is nothing that prevents your from creating this schema on its own, without the four other schemas.

    The SH schema also requires the partitioning option to be installed.



The EXAMPLE Tablespace

Before you attempt to run the schema creation scripts, you will need to first create the tablespace that will hold all of the schema's segments. Oracle uses a tablespace named EXAMPLE by default. The following SQL will create the EXAMPLE tablespace:
CREATE TABLESPACE "EXAMPLE"
LOGGING DATAFILE '/u10/app/oradata/ORA920/example01.dbf' SIZE 50M REUSE
AUTOEXTEND ON NEXT 50M MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;



Running the Schema Creation Scripts - (9i)

Finally, it is time to run the scripts necessary to create all of the Sample Schemas. If you simply want to create all Sample Schemas, Oracle provides a master script that runs all of the main scripts in their proper sequence. To call this script, you will need to provide the SYS and SYSTEM passwords as well as providing the passwords for the HR, OE, PM, all of the QS schema's, and SH users as follows:
SQL> @?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd pspwd shpwd
Here is an example call:
% sqlplus /nolog

SQL> @?/demo/schema/mksample manager change_on_install hr oe pm qs sh

NOTE: The first 4 Sample Schemas take little time to be created. The SH schema takes about 30 to 45 minutes to complete.



Running the Schema Creation Scripts - (10g)

Finally, it is time to run the scripts necessary to create all of the Sample Schemas. If you simply want to create all Sample Schemas, Oracle provides a master script that runs all of the main scripts in their proper sequence. To call this script, you will need to provide the SYSTEM and SYS passwords as well as providing the passwords for the HR, OE, PM, IX, SH, and BI schema's. You will also need to supply the name of the default tablespace to use (i.e. EXAMPLE) along with the temporary tablespace (i.e. TEMP). And finally, the last variable is the directory name (including trailing delimiter) for all log files to be written to, all as follows:
SQL> @?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd deflt_tbls temp_tbls log_dir
Here is an example call:
% sqlplus /nolog

SQL> @?/demo/schema/mksample manager change_on_install hr oe pm ix sh bi example temp $ORACLE_HOME/demo/schema/log/

NOTE: The entire process takes about 30 to 45 minutes to complete.

  If you are using Oracle10g Enterprise Edition - (Version: 10.1.0.2 to 10.1.0.4) and all ready to run the script $ORACLE_HOME/demo/schema/mksample.sql, you will notice that it is missing! In this directory, only the following files/directories are found:

  • mkplug.sql
  • mk_dir.sql
  • Directories for HR, OE, etc.

The actual sample schema scripts are not present as part of a default Enterprise Edition (EE) install. So, where do we get these missing script files? All demos are provided on the Companion CD.

You will need to execute the following steps to have access to the sample schema scripts:

  1. When installing the Companion CD, select the Oracle Home for your current database software installation (i.e. OraDb10g_home1). Also, select "Oracle Database 10g Products" from the "Install the Select a Product to Install" screen. (This is the first option)
  2. You can use the DBCA to create the sample schemas or use the master script "$ORACLE_HOME/demo/schema/mksample.sql". Also keep in mind that the mksample.sql script does not create the EXAMPLE tablespace, so you'll need to create it before running mksample.sql.
  3. If there was a patchset applied to the Oracle Database installation before installing the companion CD, (i.e. 10.1.0.4), that patchset should be installed again as the installed products in the OUI Inventory lists the patch set as an additional line under:

    • Oracle Database 10g 10.1.0.2.0
    • Oracle Database 10g 10.1.0.4.0


Page Count: 3834