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.
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?
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.