Oracle DBA FAQ - Loading and Exporting Data

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4 5 6 7

(Continued from previous part...)

What Is the Data Pump Export Utility?

Oracle Data Pump Export utility is a standalone programs that allows you to export data objects from Oracle database to operating system files called dump file set, which can be imported back to Oracle database only by Oracle Data Pump Import utility.

The dump file set can be imported on the same system or it can be moved to another system and loaded there.

The dump file set is made up of one or more disk files that contain table data, database object metadata, and control information. The files are written in a proprietary, binary format. During an import operation, the Data Pump Import utility uses these files to locate each database object in the dump file set. Because the dump files are written by the server, rather than by the client, the data base administrator (DBA) must create directory objects.

What Is the Data Pump Import Utility?

Oracle Data Pump Import utility is a standalone programs that allows you to import data objects from an Oracle dump file set into Oracle database. Oracle dump file set is written in a proprietary binary format by the Data Pump Export utility.

Import can also be used to load a target database directly from a source database with no intervening dump files. This allows export and import operations to run concurrently, minimizing total elapsed time. This is known as a network import. Data Pump Import enables you to specify whether a job should move a subset of the data and metadata from the dump file set or the source database (in the case of a network import), as determined by the import mode. This is done using data filters and metadata filters, which are implemented through Import commands.

How To Invoke the Data Pump Export Utility?

The Data Pump Export utility is distributed as executable file called "expdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "expdp" command. Here is tutorial exercise on how to invoke the export utility:

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>expdp help=y
Export: Release 10.2.0.1.0 - 

The Data Pump export utility provides a mechanism for
transferring data objects between Oracle databases. The 
utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir
     DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp'
command followed by various parameters:

   Format:  expdp KEYWORD=value or
     KEYWORD=(value1,value2,...,valueN)

   Example: expdp scott/tiger DUMPFILE=scott.dmp
     DIRECTORY=dmpdir 
               SCHEMAS=scott or TABLES=(T1:P1,T1:P2)

USERID must be the first parameter on the command line.

Keyword       Description (Default)
------------------------------------------------------------
ATTACH        Attach to existing job, e.g. ATTACH [=job name
COMPRESSION   Reduce size of dumpfile contents where valid
              keyword values are: (METADATA_ONLY) and NONE.
DIRECTORY     Directory object to be used for dumpfiles 
DUMPFILE      List of destination dump files (expdat.dmp).
FLASHBACK_SCN SCN used to set session snapshot back to.
FULL          Export entire database (N).
HELP          Display Help messages (N).
......

How To Invoke the Data Pump Import Utitlity?

The Data Pump Import utility is distributed as executable file called "impdp.exe". To invoke this utility, you should go to the "bin" directory of your Oracle server installation and run the "impdp" command. Here is tutorial exercise on how to invoke the import utility:

>cd \oraclexe\app\oracle\product\10.2.0\server\BIN

>impdp help=y
Import: Release 10.2.0.1.0 - 

The Data Pump Import utility provides a mechanism for 
transferring data objects between Oracle databases. The 
utility is invoked with the following command:

   Example: impdp scott/tiger DIRECTORY=dmpdir 
     DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' 
command followed by various parameters.

   Format:  impdp KEYWORD=value or
     KEYWORD=(value1,value2,...,valueN)

   Example: impdp scott/tiger DIRECTORY=dmpdir 
     DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

Keyword    Description (Default)
----------------------------------------------------------
ATTACH     Attach to existing job, e.g. ATTACH [=job name]
DIRECTORY  Directory object to be used for dump, log, ... 
DUMPFILE   List of dumpfiles to import from (expdat.dmp)
FULL       Import everything from source (Y).
HELP       Display help messages (N).
......

(Continued on next part...)

Part: 1 2 3 4 5 6 7

More...