logo for asktheoracle.net
Home
AskTheOracle Blog
Oracle Tips & Tricks
Oracle Training
Oracle Tutorials
PL/SQL
SQL
Advanced Tutorials
Performance Tuning
Certification
Oracle 10g
Oracle 11g
Oracle and .Net
Oracle Utilities
Developer Tools
Oracle Questions?
Oracle News
Search This Site
About Us
Disclaimer
Privacy Policy
Contact Us
leftimage for asktheoracle.net

A Guide To Oracle Export and Import

The Oracle export and import utilities are provided by Oracle as a way of making logical backups of the database to complement physical backups made of the data files either by using RMAN or the o/s copy utility and of copying data from one database to another. This may be carried out for testing and/or training purposes or when upgrading to a new release of Oracle.
In Oracle 10g and later releases, these utilities are superseded by  Oracle Data Pump which acts in much the same way as the original export and import utilities but is designed to make the process of transferring data from one database to another faster and more secure. Oracle Data Pump also provides a new network mode which removes  the need to create intermediate files.

How do the Export and Import utilities  work and what are they used for?

The Oracle export and import utilities are used to provide logical backups of objects in the database to supplement the physical backups made for example with RMAN (the Recovery Manager).

These utilities can work on several different levels: (full) database, tablespace, user (schema) or table levels. They can only be used for logical backup and recovery (for example to recover the data of a

Oracle Tips & Tricks
to SKYROCKET Your Career!

Don't waste hours scouring the Internet for Oracle tips. Subscribe to our ezine and get them delivered straight to your in box.
table accidentally truncated by a user) because the export utility can only take a snapshot of the data at the time it is run and unlike with objects restored from physical backups can't be rolled forward by using the redo logs.

Backups made using the Oracle export utility are performed with the database open so in this sense they are hot (online) backups. However  recovered data can't be rolled forward as this would require the redo logs from the same point in time as the export. This means exports must be performed when there are no updates occurring on the objects being exported to ensure that the backup is consistent and can therefore be used for recovery.

Use of the export utility is also a good way to capture the metadata  
of the database/schemas/tables as the export process generates the SQL statements to recreate the database objects (with or without the associated data) and stores them in the export dump file (by default called expdat.dmp).

The exported file is in a proprietary (binary) format and can only be read by the import utility, and not by the other utilities such as RMAN or SQL*Loader.

A new feature was added to the Oracle export utility in Oracle 9i to enable the export of just a subset of the table data by using a query to specify the data to be exported.

Summary

Although no longer supported in Oracle 11g (except for downgrading to a previous release), the Oracle export and import utilities have for many years been the primary utilities used for supplemental (logical) backups and for transferring data between databases. They are relatively easy to use and understand and reasonably quick to run for small datasets.

The main disadvantage to using them has been that the availability of an export dump file containing unencrypted data from a production database is a security risk - anyone with access to the file can copy it and load it into another database. For this and other reasons, these utilities are being replaced with Oracle Data Pump import and export.

Looking for expert Oracle training? We offer Oracle training in the UK and New Zealand for both developers and dbas. Contact us today and discover how we can help you slash costs and explode productivity.

Streamline your Oracle systems with expert Oracle consulting services from Smartsoft. With skills honed to a fine edge over many years with myriad clients in a variety of industries, our consultants have the knowledge and experience to help you. Click here for a consultation application form.