Install the Sample Schema I am going to use the mksample.sql to create the sample schema. This is the primary script and will invoke other script to create the correspond schema. With this simplicity I personally found difficulties in creating Sample Schemas in Oracle 11g database. In fact I could not do it in a standard way with Oracle installation binaries before 11.2.0.3. If somebody experienced similar issues, I give below the way how to install Sample Schemas manually. Manually Installing Sample Schemas. Install Oracle Database Examples (Companion CD, part of the media kit). Installing scott schema for oracle 11g express. Uninstall Sample Schemas. If you need to remove the sample schemas from the installation, run script drop_sch.sql. Script drop_sch.sql uses two parameters: systempwd is the password for SYSTEM user, andspool_file_name is the name of the spool file that captures the log of the operation.

Posted Jun 23, 2010

By Steve Callan

Having a re-usable dump file for installing Oracle Database Sample Schemas is much more convenient than the current method. Would you rather download a compressed file containing one (or several) dump files, including a parameter file suitable for use during an import session or job or use the Oracle Universal Installer?

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:

Export parameter file contents

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.

» See All Articles by Columnist Steve Callan

How To Install Sample Schemas In Oracle 11g