[?] Subscribe To This Site

XML RSS
Add to Google
Add to My Yahoo!
Add to My MSN
Subscribe with Bloglines


Home
Smartsoft Blog
Oracle Training
Oracle 9i
Oracle 10g
Oracle 11g
Oracle App Server
Oracle Utilities
Backup and Recovery
Oracle Certification
Oracle Tutorials
PL/SQL
Free SQL Tutorial
SQL
News
Oracle Resources
Oracle Secrets Signup
Oracle and .Net
Contact Us

Oracle Export Import

Oracle export import utilities are provided by Oracle as a way of making logical backups of the database and of copying data from one database to another 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 the Oracle Data Pump import and export utility. This acts in much the same way as the original uutilities but is designed to make the process of transferring data from one database to another much faster and provides a new network mode which avoids the need to create intermediate files.

How do they work and what are they used for?

The Oracle export 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: database, tablespace, user (schema) and tables. They can only be used for logical backup and recovery (for example to recover the data of a table accidentally truncated by a user) because they only take a snapshot of the data at the time they are run and unlike with objects restored from physical backups can't be rolled forward by using the redo logs.

Backups using the export utility are performed with the database open so they are in a sense online backups, however (because recovered data can't be rolled forward) exports must be performed when there are no updates occurring on the objects being exported whilst the export is in progress. This is to ensure that the backup is consistent and can therefore be used for recovery.

Using the export utility is also a good way of capturing 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 format which can only be read by the import utility, and not by the other utilities such as RMAN or SQL*Loader.

In Oracle 9i a new feature was added to the export utility to enable the export of a subset of the table data by use of a provided query (select clause) to retrieve a subset of the data.

Summary

Although no longer supported in Oracle 11g (except for downgrading to a previous release), Oracle export 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.

Return from Oracle export import to backup & recovery


footer for Oracle export import page