分类: Oracle
2010-03-22 15:02:42
Oracle Data Pump enables high-speed movement of data and metadata from one database to another. This technology is the basis for the following Oracle Database data movement utilities:
· Data Pump Export (Export)
Export is a utility for unloading data and metadata into a set of operating system files called a dump file set. The dump file set is made up of one or more binary files that contain table data, database object metadata, and control information.
· Data Pump Import (Import)
Import is a utility for loading an export dump file set into a database. You can also use Import to load a destination database directly from a source database with no intervening files, which allows export and import operations to run concurrently, minimizing total elapsed time.
Oracle Data Pump is made up of the following distinct parts:
· The command-line clients expdp
and impdp
These client make calls to the DBMS_DATAPUMP
package to perform Oracle Data Pump operations (see ).
· The DBMS_DATAPUMP
PL/SQL package, also known as the Data Pump API
This API provides high-speed import and export functionality.
· The DBMS_METADATA
PL/SQL package, also known as the Metadata API
This API, which stores object definitions in XML, is used by all processes that load and unload metadata.
The REUSE_DUMPFILES
parameter can be used to prevent errors being issued if the export attempts to write to a dump file that already exists.
REUSE_DUMPFILES={Y | N}
When set to "Y", any existing dumpfiles will be overwritten. When the default values of "N" is used, an error is issued if the dump file already exists.
expdp test/test schemas=TEST directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
reuse_dumpfiles=y
The TRANSPORTABLE
parameter is similar to the TRANSPORT_TABLESPACES
parameter available previously in that it only exports and imports metadata about a table, relying on you to manually transfer the relevent tablespace datafiles. The export operation lists the tablespaces that must be transfered. The syntax is shown below.
TRANSPORTABLE = {ALWAYS | NEVER}
The value ALWAYS
turns on the transportable mode, while the default value of NEVER
indicates this is a regular export/import.
The following restrictions apply during exports using the TRANSPORTABLE
parameter:
Some extra restictions apply during import operations:
NETWORK_LINK
parameter must be specified during the import operation. This parameter is set to a valid database link to the source schema.
TRANSPORT_DATAFILES
parameter is used to identify the datafiles holding the table data. Examples of the export and import operations are shown below.
expdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=expdpTEST.log
transportable=ALWAYS
impdp system tables=TEST1.TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
transportable=ALWAYS network_link=DB11G transport_datafiles='/u01/oradata/DB11G/test01.dbf'
This parameter allows a table to be renamed during the import operations performed using the TRANSPORTABLE
method. It can also be used to alter the base table name used during PARTITION_OPTIONS
imports. The syntax is shown below.
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
An example is shown below.
impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
remap_table=TEST.TAB1:TAB2
Existing tables are not renamed, only tables created by the import.
SKIP_CONSTRAINT_ERRORS
During import operations using the external table acces method, setting the DATA_OPTIONS
parameter to SKIP_CONSTRAINT_ERRORS
allows load operations to continue through non-deferred constraint violations, with any violations logged for future reference. Without this, the default action would be to roll back the whole operation. The syntax is shown below.
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
An example is shown below.
impdp test/test tables=TAB1 directory=TEST_DIR dumpfile=TEST.dmp logfile=impdpTEST.log
data_options=SKIP_CONSTRAINT_ERRORS
This parameter has no impact on deferred constraints, which still cause the operation to be rolled back once a violation is detected. If the object being loaded has existing unique indexes or constraints, the APPEND
hint will not be used, which may adversely affect performance.
The TABLES
parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:
expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
For example output files see and .
The OWNER
parameter of exp has been replaced by the SCHEMAS
parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:
expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
The FULL
parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:
expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log
impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export:
expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
TABLE_EXISTS_ACTION – The original imp would allow rows to be appended to existing tables
if IGNORE=Y was specified. The TABLE_EXISTS_ACTION parameter for Data Pump
impdp provides four options:
1. SKIP is the default: A table is skipped if it already exists.
2. APPEND will append rows if the target table’s geometry is compatible. This
is the default when the user specifies CONTENT=DATA_ONLY.
3. TRUNCATE will truncate the table, then load rows from the source if the
geometries are compatible and truncation is possible. For example, it is not
possible to truncate a table if it is the target of referential constraints.
4. REPLACE will drop the existing table, then create and load it from the
source.
The TABLE_EXISTS_ACTION=APPEND
parameter allows data to be imported into existing tables.