Oracle’s Sample Schemas: Saying Goodbye to Scott

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

Steve Callan
Steve Callan
Steve is an Oracle DBA (OCP 8i and 9i)/developer working in Denver. His Oracle experience also includes Forms and Reports, Oracle9iAS and Oracle9iDS.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles