Sunday, July 13, 2008

Data pump Export (expdp) 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.

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 Export Modes: One of the most significant characteristics of an export operation is its mode, because the mode largely determines what is exported. Export provides different modes for unloading different portions of the database. The mode is specified on the command line, using the appropriate parameter. The available modes are as follows:

1. Full Export Mode: A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. Example:

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

2. Schema Mode: A schema export is specified using the SCHEMAS parameter. This is the default export mode.
Example:

expdp hr/hr SCHEMAS=hr DIRECTORY=dump_dir DUMPFILE=hr.dmp FLASHBACK_SCN=65610662

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
EXCLUDE=TABLE:"like'EMP'" DUMPFILE=hr_exclude_emp.dmp

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
INCLUDE=TABLE:"like'EM%'" DUMPFILE=hr.dmp


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

3. Table Mode : A table export is specified using the TABLES parameter. In table mode, only a specified set of tables, partitions, and their dependent objects are unloaded.
Example:

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=prod7
INCLUDE=TABLE:"like'HR_%'" DUMPFILE=hr_tables.dmp

expdp hr/hr TABLES=login,emp,salary DIRECTORY=dump_dir
DUMPFILE=hr_table.dmp


expdp hr/hr TABLES=emp DIRECTORY=dump_dir
DUMPFILE=emp-20080106.dmp FLASHBACK_SCN=65619542


expdp hr/hr DIRECTORY=dump_dir DUMPFILE=emp.dmp
TABLES=EMP:EMP_PART_2005

expdp hr/hr DIRECTORY=dump_dir SCHEMAS=hr
EXCLUDE=TABLE:"like'EMP'" EXCLUDE=TABLE:"SALARY'"



4. Tablespace Mode : A tablespace export is specified using the TABLESPACES parameter. In tablespace mode, only the tables contained in a specified set of tablespaces are unloaded. If a table is unloaded, its dependent objects are also unloaded. Both object metadata and data
are unloaded. In tablespace mode, if any part of a table resides in the specified set, then that table and all of its dependent objects are exported. Privileged users get all tables.
Example:

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



5. Transportable Tablespace Mode : A transportable tablespace export is specified using the TRANSPORT_TABLESPACES parameter. In transportable tablespace mode, only the metadata for the tables (and their dependent objects) within a specified set of tablespaces are unloaded. This allows
the tablespace datafiles to then be copied to another Oracle database and incorporated using transportable tablespace import. This mode requires that you have the EXP_FULL_DATABASE role.
Unlike tablespace mode, transportable tablespace mode requires that the specified tables be completely self-contained. That is, the partitions of all tables in the set must also be in the set. Transportable tablespace exports cannot be restarted once stopped. Also, they cannot have a degree of parallelism greater than 1.
Example:

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



No comments: