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.
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
Having a re-usable dump file for installing
the sample schemas is going to be much more convenient
than the current method.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
and include the URL for the page.
