Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle Data Pump Expert tips

Oracle Database Tips by Burleson Consulting

 
June 23, 2015
 

 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.

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.

   

 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.