Oracle's Sample Schemas: Saying Goodbye to Scott

June 9, 2004

Did you know that Oracle's "Scott" schema is slated for the bone yard? How many thousands of people have learned SQL, SQL*Plus, and PL/SQL on the back of poor old Scott? I would venture to say that the current generation of Oracle DBAs and developers are all familiar with the Scott schema and the schema is probably one of the most well known pieces of Oracle. Scott's schema is useful for many things, but it lacks the horsepower and structure to showcase some of Oracle's more robust features.

Well, if Scott is going by the wayside, who is taking his place? Unfortunately, the sample schema that comes with 9i and later is a bit impersonal. Get used to names such as HR, OE, PM, QS, and SH. In fact, if you created the seed database during the installation of Oracle, you may have already "met" these new users. You can see these names (highlighted in blue) in the dba_users table.

Select username from dba_users;

SYS

OUTLN

QS_CBADM

PM

ANONYMOUS

SYSTEM

WMSYS

QS_OS

OE

WKSYS

DBSNMP

ORDSYS

QS_ES

RMAN

WKPROXY

SCOTT

ORDPLUGINS

QS_WS

QS_CS

ODM

SH

MDSYS

QS

QS_CB

ODM_MTR

HR

CTXSYS

QS_ADM

XDB

OLAPSYS

By way of introduction, the two-letter names stand for the following:

HR - Human resources, basic topics, supports Oracle Internet Directory

OE - Order entry, intermediate topics, various datatypes

PM - Product media, used for multimedia datatypes

QS - Queued shipping, shows advanced queuing, named IX in 10g

SH - Sales history, large amount of data, analytic processing

There is also a sub-schema named OC (for Online Catalog, under the OE schema) used for object-relational examples.

One good thing about the Scott schema is it that can be dropped and re-created with two commands ("drop user scott cascade" and then run either of utlsampl.sql or demobld.sql). With the new sample schema, the users have a dependency upon one another. Oracle points out the order of the creation for you in case you want to create these schemas manually. The names - HR, OE, PM, QS, and SH - are in alphabetical order, and that order specifies the order of creation.

All installation options (personal, standard and enterprise) receive the first four schemas, and only the enterprise edition with partitioning receives the SH schema. For your convenience, Oracle provides a master script that creates the entire sample schema, and it is found in ORACLE_HOME\demo (the mksample SQL script).

Side note: how do you know if you have the partitioning option installed? Since this article is geared towards introducing the sample schema and is not about partitioning, one quick way is to look at a few items. First, if the SH schema was not created, then SH's partitioned tables probably do not exist. Second, if SH exists, look at SH's tables. You can also query xxx_part_tables, where xxx is in dba, all, and user.

SQL> show user
USER is "SH"
SQL> select table_name, partitioned
  2  from user_tables;

TABLE_NAME                     PAR
------------------------------ ---
CAL_MONTH_SALES_MV             NO
CHANNELS                       NO
COSTS                          YES <--
COUNTRIES                      NO
CUSTOMERS                      NO
FWEEK_PSCAT_SALES_MV           NO
MVIEW$_EXCEPTIONS              NO
MV_CAPABILITIES_TABLE          NO
PLAN_TABLE                     NO
PRODUCTS                       NO
PROMOTIONS                     NO
REWRITE_TABLE                  NO
SALES                          YES <--
SALES_TRANSACTIONS_EXT         NO
TIMES                          NO

15 rows selected.

I have used the SH schema in previous articles because of the amount of data contained in the SALES table (around a million rows). The entire set of sample schemas contains just over 3 million rows of data - that is certainly a good-sized bit of data to play with. Unfortunately, when all you want is to look at a few rows of a table, you are going to have to restrict or filter the data. A "select * from emp" returns 14 rows, and a "select * from employees" in the HR schema returns 107 rows (and the department table returns 27).

Although Oracle has not stated it will remove the Scott SQL script files from the program files, it may not be a bad idea to squirrel away the two scripts (demobld and utlsampl, respectively found in the sqlplus\demo and rdbms\admin directories) for future use and reference. Moreover, as for Adams, Jones, Clark and Blake, well, we hardly knew you.

In Oracle Database Sample Schemas (the 10g version) and Oracle9i Sample Schemas, you can see diagrams of the schemas. The Queued Shipping schema is fairly complex if all you have ever used is the Scott schema.

If you are learning how to draw entity-relationship diagrams and how to create tables, you owe it to yourself to study Oracle's diagrams and scripts. By viewing the scripts, you will see examples on how to write various "create table" and "alter table" statements. You will also see several tables with "PCTFREE 5" specified. Why do you suppose that was added? In addition, why was NOLOGGING added to the "CREATE TABLE sales" statement?

In Closing

By "formally" introducing the sample schema into its documentation and programs of instruction (online and instructor-led), students learning Oracle and instructors teaching Oracle now have better resources at hand with which to use and explore when it comes to using more of Oracle's capabilities. Learning to understand what happens with "select * from emp" is pretty much the first step on the path of what Oracle has to offer with respect to complex datatypes, objects and constructs. The sample schema is free, so jump on in and learn more about materialized views, object-relational tables and partitions. The job you want or the job you have may depend upon your knowing more about Oracle's features.

» See All Articles by Columnist Steve Callan








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers