Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jun 9, 2004

Oracle's Sample Schemas: Saying Goodbye to Scott

By Steve Callan

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;































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
------------------------------ ---
CHANNELS                       NO
COSTS                          YES <--
COUNTRIES                      NO
CUSTOMERS                      NO
PLAN_TABLE                     NO
PRODUCTS                       NO
PROMOTIONS                     NO
REWRITE_TABLE                  NO
SALES                          YES <--
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

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM