oracle data dump dir,Oracle Data Pump (Expdp and Impdp)

Existing tables are not renamed, only tables created by the import.

SKIP_CONSTRAINT_ERRORS

During import operations using the external table acces method, setting theDATA_OPTIONSparameter toSKIP_CONSTRAINT_ERRORSallows 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.logdata_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, theAPPENDhint will not be used, which may adversely affect performance.

Table Exports/Imports

TheTABLESparameter 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.logimpdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

For example output files seeand.

Schema Exports/Imports

TheOWNERparameter of exp has been replaced by theSCHEMASparameter 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.logimpdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

TheFULLparameter 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.logimpdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

TheINCLUDEandEXCLUDEparameters can be used to limit the export/import to specific objects. When theINCLUDEparameter is used, only those objects specified by it will be included in the export. When theEXCLUDEparameter 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.TheTABLE_EXISTS_ACTION=APPENDparameter allows data to be imported into existing tables.