Explore Oracle 10g's updated sample schemas

Each new release of Oracle database brings with it new features to explore. It can be a challenge to create adequate sample tables to play with a given feature.

But since Oracle 9i, Oracle has had a robust set of sample schemas you can use to learn new features. The Oracle Documentation also uses these tables in its examples, so you can often simply cut and paste an example from the documentation to see how it works.

There are five sample schemas in the set. All of the schemas feature some aspect of the Oracle database and use consistent primary keys throughout so that the joins between them work easily.

Here is an overview of the five schemas:

|> Human Resources (HR): This is the simplest of the schemas. HR resembles the older SCOTT schema in that it has departments and employees. The seven tables use fundamental datatypes, and are suitable for learning basic features.
|> Order Entry (OE): This is a slightly more complex schema. OE adds customers, products, and orders to the model. These more complex layouts can be used to explore additional data types, including nested tables and additional table options such as Index Organized Tables (IOTs). An object-relational example called Online Catalog (OC) is also stored within this schema for testing Oracle's object-oriented features.
|> Product Media (PM): The focus of this schema is multimedia data types.
|> Information Exchange (IX): This schema is designed to showcase Oracle's inter-process communication features of Advanced Queuing. In fact, in versions before 10g, this schema was called QS for Queued Shipping.
|> Sales History (SH): This schema isn't especially complex. SH contains many more rows of data than the other schemas. It's a great place to try out SQL Analytic Functions, the MODEL clause, etc.

Installation is a bit more complex than just running the demobld.sql script that created the SCOTT user. The easiest way is to select the check box for Sample Schemas during the Database Creation Assistant (DBCA). Starting in Oracle 10g Release 2, DBCA creates sample schemas that are locked and expired: you'll have to unlock them and reset the passwords to use them.

To run the scripts manually, locate them in $ORACLE_HOME/demo (Oracle 9i) or on the companion CD (Oracle 10g). A master script, mksample.sql, calls multiple other scripts to set up the schemas. You can also use these scripts individually to drop the contents of a particular schema and re-create it.

These sample schemas are much more complex than the historical SCOTT schema's four tables. In fact, the Oracle Documentation even has an entire book called Oracle Database Sample Schemas, which describes how to use the sample schemas.