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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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;

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date