Oracle Export & Import

Data Pump

exp and imp are utilities present in the $ORACLE_HOME/bin directory and are installed when Oracle is installed. Their prime purpose is to move logical objects out of and into the database respectively – for example dumping all of the tables owned by a user to a single file is achieved using the exp utility.

exp and imp are the executables that allow to make exports and imports of data objects (such as tables). Therefore, logical backups can be made with exp.

exp/imp allow to transfer the data accross databases that reside on different hardware platforms and/or on different Oracle versions. If the data is exported on a system with a different Oracle version then on that on which it is imported, imp must be the newer version. That means, if something needs to be exported from 10g into 9i, it must be exported with 9i’s exp.

imp doesn’t re-create an already existing table. It either errors out or ignores the errors.

In order to use exp and imp, the catexp.sql script must be run. catexp.sql basically creates the exp_full_database and imp_full_database roles.

It is found under $ORACLE_HOME/rdbms/admin:

SQL> @?/rdbms/admin/catexp

The Export and Import tools support four modes of operation:

  • FULL : Exports all the objects in all schemas 
  • OWNER : Exports objects only belonging to the given OWNER 
  • TABLES : Exports Individual Tables 
  • TABLESPACE : Export all objects located in a given TABLESPACE.

EXPORT & IMPORT are used for the following tasks:

  • Backup ORACLE data in operating system files
  • Restore tables that where dropped
  • Save space or reduce fragmentation in the database
  • Move data from one owner to another

EXPORT Utility Commands:

Exporting of ORACLE database objects is controlled by parameters. To get familiar with EXPORT parameters type:
exp help=y
You will get a short description and the default settings will be shown

Example of Exporting Full Database

$exp USERID=scott/tiger FULL=y FILE=myfull.dmp

In the above command, FILE option specifies the name of the dump file, FULL option specifies that you want to export the full database, USERID option specifies the user account to connect to the database.

Note: To perform full export the user should have DBA or EXP_FULL_DATABASE privilege.

Example of Exporting Schemas

$exp USERID=scott/tiger OWNER=(SCOTT,ALI) FILE=exp_own.dmp

The above command will export all the objects stored in SCOTT and ALI’s schema.

Exporting Individual Tables

$exp USERID=scott/tiger TABLES=(scott.emp,scott.sales) FILE=exp_tab.dmp

This will export scott’s emp and sales tables.

Exporting Consistent Image of the tables

If you include CONSISTENT=Y option in export command argument then, Export utility will export a consistent image of the table i.e. the changes which are done to the table during export operation will not be exported.

Using imp/exp across different Oracle versions

If exp and imp are used to export data from an Oracle database with a different version than the database in which is imported, then the following rules apply:

  1. Exp must be of the lower version
  2. Imp must match the target version.

IMPORT Utility Commands:

Like EXPORT the IMPORT utility is controlled by parameters. To get familiar with these parameters type: imp help=y
You will get a short description of usage and default settings of parameters.
To start IMPORT simply type imp. You will be prompted for your ORACLE userid, password. The next prompts depend on what you answer.

Example Importing Individual Tables

$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(emp,dept)

This command will import only emp, dept tables into Scott user and you will get a output similar to as shown below

Export file created by EXPORT:V10.00.00 via conventional path

import done in WE8DEC character set and AL16UTF16 NCHAR character set

. importing SCOTT’s objects into SCOTT

. . importing table “DEPT” 4 rows imported

. . importing table “EMP” 14 rows imported

Import terminated successfully without warnings.

Example, Importing Tables of One User account into another User account

For example, suppose Ali has exported tables into a dump file mytables.dmp. Now Scott wants to import these tables. To achieve this Scott will give the following import command

$imp scott/tiger FILE=mytables.dmp FROMUSER=ali TOUSER=scott

Then import utility will give a warning that tables in the dump file was exported by user Ali and not you and then proceed.

Example Importing Tables Using Pattern Matching

Suppose you want to import all tables from a dump file whose name matches a particular pattern. To do so, use “%” wild character in TABLES option. For example, the following command will import all tables whose names starts with alphabet “e” and those tables whose name contains alphabet “d”

$imp scott/tiger FILE=myfullexp.dmp FROMUSER=scott TABLES=(a%,%d%)

Migrating a Database across platforms.

The Export and Import utilities are the only method that Oracle supports for moving an existing Oracle database from one hardware platform to another. This includes moving between UNIX and NT systems and also moving between two NT systems running on different platforms.

The following steps present a general overview of how to move a database between platforms.

  1. As a DBA user, issue the following SQL query to get the exact name of all tablespaces. You will need this information later in the process.

SQL> SELECT tablespace_name FROM dba_tablespaces;

  1. As a DBA user, perform a full export from the source database, for example:

exp system/manager FULL=y FILE=myfullexp.dmp

  1. Move the dump file to the target database server. If you use FTP, be sure to copy it in binary format (by entering binary at the FTP prompt) to avoid file corruption.
  2. Create a database on the target server.
  3. Before importing the dump file, you must first create your tablespaces, using the information obtained in Step 1. Otherwise, the import will create the corresponding datafiles in the same file structure as at the source database, which may not be compatible with the file structure on the target system.
  4. As a DBA user, perform a full import with the IGNORE parameter enabled:

> imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp

Using IGNORE=y instructs Oracle to ignore any creation errors during the import and permit the import to complete.