I wrote an introductory article a few years ago (actually more like six) about the new Oracle Database Sample Schemas you can have installed within a database. The installation method via Oracle Universal Installer has changed a little bit since then. Where and when you install a companion CD probably dissuaded users from even trying to install the schemas in the first place. In fact, if you didn't install the sample schemas in a newly created database (via DBCA) session, the work it would take to undo that decision (that is, do the install later) would be well, let's just say it is almost infinitely harder than it needs to be.

First and foremost, the official documentation for installing after the fact is outright wrong. The documentation references non-existent files and directories, and to make things worse, OTN contributes to the problem by providing watered down versions of (at least) the HR and OE schemas in some of the Oracle by Example modules. On some after-the-fact installations, a spatial data type within one table causes problems during the CREATE TABLE statement (error message about how the database does not support spatial datatypes).

Why not make the sample schemas one of two things (or even both); first, how about a complete zip file in and of itself, containing nothing more than all of the DDL and insert statements? This would be similar to what Microsoft does (or used to do, but another site maintains the files now) in terms of having you download a Windows MSI installer file for a particular database (AdventureWorks and its variations, to name one series of ready-to-go databases). Second, which is a more interesting approach that would reinforce feature usage and practical application, is to simply make the sample schemas a Data Pump export dump file. You could download a compressed file that when expanded would further contain either one dump file, or several dump files (one for each schema). Also included in the package would be a parameter file suitable for use during an import session or job.

With a single dump file and a starter-like parameter file, you would have the opportunity to edit the file for metadata filters (to INCLUDE or EXCLUDE schemas). You could also take care of tablespace mappings if your target database did not include a USERS or EXAMPLES tablespace. The simplicity and efficiency of this approach, compared to the current method (go download a half-GB file, install it into an existing Oracle home, start watching Oracle TV, aka OUI screenshots), and well, I'm sure you get the idea. The problem is that Oracle Corporation does not.

Just out of curiosity, when you do install Oracle right now, and when you get to the Database Configuration Assistant phase where the seed database is being created, where are the DDL and insert statements coming from? The point of the question is that we can deduce Oracle has a method of doing the schema creation and data population today, but it is not surfaced to us (the users) in a convenient or consistently accessible manner.

Virtually all code snippets and examples within the entire documentation collection for a release are based upon one or more of the sample schemas. The documentation and access to the sample schemas should go hand-in-hand as far as ease of access is concerned. I can see having the other somewhat less than mainstream example sets being installed under the current method. The two people who actually use XML DB, for example, probably aren't ever going to complain about what it takes to install XML DB examples. They're probably happy enough knowing the examples exist in the first place, now if we can only figure out what XML DB can be used for.

Yet another variation for installation, also reinforcing feature usage and exposure to relatively more advanced tasks would be to have the schemas contained within a standalone database file. How could you take a standalone data file and plug it into an existing database? Isn't that the whole idea of transportable tablespaces? So along with the datafile comes one other file, namely, the Data Pump export dump file of the tablespace's metadata.

To back up words with action, I'll get the ball rolling on this and post a dump file that can be downloaded here.

As a review of concepts, suppose you want to install the sample schemas in your Linux development environment. Knowing ahead of time that the dump file you download will have been generated on a Windows platform, is that going to matter to you? If you think so, why is that? Let's go back to pre-9i days, and all you have are original export and import. How did you do a platform migration back then? Export out with the lower version of Oracle's exp executable, whatever it was running on, and then import in with the newer version's imp executable? Where did the platform version come into play? It didn't, and it still doesn't.

It's what's in the dump file that matters, not which platform the file was created on. Or, at least not until we get to the transportable tablespace approach and the byte ordering of the file may matter, depending on the platforms involved. Aside from that one issue, Oracle doesn't care where the dump file was created because what is inside the file is the only thing matters.

The command and export parameter file are going to be as simple as this:

C: > expdp system parfile=export_samples.par

Export parameter file contents

SCHEMAS=HR, BI, OE, SH, IX
DUMPFILE=exported_sample_schemas.dmp 
DIRECTORY=data_pump_dir
LOGFILE=exported_sample_schemas.log
The import parameter file will include what is shown below.
REMAP_SCHEMAS=HR:HR, BI:BI, OE:OE, SH:SH, IX:IX
DUMPFILE=exported_sample_schemas.dmp 
DIRECTORY=data_pump_dir
LOGFILE=imported_sample_schemas.log 

In Closing

If Data Pump is new to you, take a look at the Oracle by Example demo of using Data Pump, available here. Having a re-usable dump file for installing the sample schemas is going to be much more convenient than the current method.

Additional Resources

Oracle 10g: Exploring Data Pump
Whats in an Oracle Schema?
The Oracle 10g Data Pump API Speeds Up the ETL Process
Oracle Overview of Oracle Data Pump

Back to DBAsupport.com