Wednesday, July 16, 2008

Data pump Import (impdp) patt #2

2. Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter because parameter files cannot be nested. The use of parameter files is recommended if you are using parameters whose values require quotation marks. See Use of Quotation Marks On the Data Pump Command Line.


3. Interactive-Command Interface: Stops logging to the terminal and displays the Export prompt, from which you can enter various commands, some of which are specific to interactive-command mode. This mode is enabled by pressing Ctrl+C during an export operation started with the command-line interface or the parameter file interface. Interactive-command mode is also enabled when you attach to an executing or stopped job. For a complete description of the commands available in interactive-command


ADD_FILE - Add additional dump files.

CONTINUE_CLIENT - Exit interactive mode and enter logging mode.

EXIT_CLIENT - Stop the export client session, but leave the job running.

FILESIZE - Redefine the default size to be used for any subsequent dump files.

HELP - Display a summary of available commands.

KILL_JOB - Detach all currently attached client sessions and kill the current job.

PARALLEL - Increase or decrease the number of active worker processes for the current job. This command is valid only in the Enterprise Edition of Oracle Database 10g.

START_JOB - Restart a stopped job to which you are attached.

STATUS - Display detailed status for the current job and/or set statusinterval.

STOP_JOB - Stop the current job for later restart.



Data Pump Import Modes


Full Import Mode

A full import is specified using the FULL parameter. In full import mode, the entire content of the source (dump file set or another database) is loaded into the target database. This is the default for file-based imports. You must have the IMP_FULL_DATABASE role if the source is another database.
Cross-schema references are not imported for non-privileged users. Example:

impdp system/password DIRECTORY=dpump_dir DUMPFILE=expfull.dmp FULL=Y

Schema Mode

A schema import is specified using the SCHEMAS parameter. In a schema import, only objects owned by the specified schemas are loaded. The source can be a full, table,tablespace, or schema-mode export dump file set or another database. If you have the IMP_FULL_DATABASE role, then a list of schemas can be specified and the schemas themselves (including system privilege grants) are created in the database in addition to the objects contained within those schemas. Cross-schema references are not imported for non-privileged users unless the other schema is remapped to the current schema. Example


impdp hr/hr SCHEMAS=hr DIRECTORY=dump_dir
DUMPFILE=hr-20080107.dmp

impdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
ESTIMATE_ONLY = Y EXCLUDE=TABLE:"like 'EMP":"EMP_2005'"

impdp system/pssword SCHEMAS=hr DIRECTORY=dump_dir
DUMPFILE=hr20080120.dmp REMAP_SCHEMA=hr:test

impdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr INCLUDE=TABLE:"like'EMP_%'" DUMPFILE=nr_emp_tables.dmp TABLE_EXISTS_ACTION=REPLACE

Table Mode

A table-mode import is specified using the TABLES parameter. In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, tablespace, or table-mode export dump file set or another database. You must have the IMP_FULL_DATABASE role to specify tables that are not in your own schema.

impdp hr/hr TABLES=emp DIRECTORY=dump_dir CONTENT=DATA_ONLY DUMPFILE=emp-20080106.dmp

impdp hr/hr TABLES=login DIRECTORY=dump_dir DUMPFILE=hr_login_table.dmp TABLE_EXISTS_ACTION=REPLACE


Tablespace Mode

A tablespace-mode import is specified using the TABLESPACES parameter. In tablespace mode, all objects contained within the specified set of tablespaces are loaded, along with the dependent objects. The source can be a full, schema, tablespace, or table-mode export dump file set or another database. For unprivileged users, objects not remapped to the current schema will not be processed.

impdp system/password DIRECTORY=dpump_dir DUMPFILE=tbs.dmp
TABLESPACES=tbs_4, tbs_5, tbs_6


Transportable Tablespace Mode

A transportable tablespace import is specified using the TRANSPORT_TABLESPACES parameter. In transportable tablespace mode, the metadata from a transportable tablespace export dump file set or from another database is loaded. The datafiles specified by the TRANSPORT_DATAFILES parameter must be made available from the
source system for use in the target database, typically by copying them over to the target system.


impdp system/password DIRECTORY=dpump_dir DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tbs_1 TRANSPORT_FULL_CHECK=Y LOGFILE=tts.log



Post a Comment