[Oracle] Data Pump 详细使用教程（5）- 命令交互模式

[Oracle] Data Pump 详细使用教程（1）- 总览

[Oracle] Data Pump 详细使用教程（2）- 总览

[Oracle] Data Pump 详细使用教程（3）- 总览

[Oracle] Data Pump 详细使用教程（4）- 总览

[Oracle] Data Pump 详细使用教程（5）- 总览

当我们起了一个datapump job之后，可以通过v$session_longops查看当前进度。

USERNAME - job owner  OPNAME - job name  TARGET_DESC - job operation  SOFAR - megabytes transferred thus far during the job  TOTALWORK - estimated number of megabytes in the job  UNITS - megabytes (MB)  MESSAGE - a formatted status message of the form:  'job_name: operation_name : nnn out of mmm MB done'  

SYS@TEST16>select username,opname,sofar,TOTALWORK,UNITS,message from v$session_longops where opname='SYS_EXPORT_FULL_03';USERNAME        OPNAME                    SOFAR  TOTALWORK UNITS MESSAGE--------------- -------------------- ---------- ---------- ----- ------------------------------------------------------------SYSTEM          SYS_EXPORT_FULL_03         4737      35368 MB    SYS_EXPORT_FULL_03: EXPORT : 4737 out of 35368 MB done

但有时候单单监控是不够的，我们可能还需要修改相应的JOB，这时我们就需要进行datapumo的命令交互模式。

有两种方式可以进入命令交互模式，分别是：

1. 在logging模式下按ctrl+C

2. expdp or impdp attach=SYSTEM.SYS_EXPORT_FULL_03

expdp交互模式的命令如下：

ActivityCommand Used

Add additional dump files.

ADD_FILE

Exit interactive mode and enter logging mode.

CONTINUE_CLIENT

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

EXIT_CLIENT

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

FILESIZE

Display a summary of available commands.

HELP

Detach all currently attached client sessions and terminate the current job.

KILL_JOB

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 11g.

PARALLEL

Restart a stopped job to which you are attached.

START_JOB

Display detailed status for the current job and/or set status interval.

STATUS

Stop the current job for later restart.

STOP_JOB

impdp的交互模式命令如下：

ActivityCommand Used

Exit interactive-command mode.

CONTINUE_CLIENT

Stop the import client session, but leave the current job running.

EXIT_CLIENT

Display a summary of available commands.

HELP

Detach all currently attached client sessions and terminate the current job.

KILL_JOB

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

PARALLEL

Restart a stopped job to which you are attached.

START_JOB

Display detailed status for the current job.

STATUS

Stop the current job.

STOP_JOB

下面以expdp为例，介绍几个常用命令（如果忘记命令，敲万能的help）。

1. status：查看当前job的状态，如完成的百分比、并行度等，每个worker代表一个并行进程。

Export> statusJob: SYS_EXPORT_FULL_03  Operation: EXPORT                           Mode: FULL                             State: EXECUTING                        Bytes Processed: 8,357,285,928  Percent Done: 23  Current Parallelism: 2  Job Error Count: 0  Dump File: /home/oracle/dump/full_%u.dmp  Dump File: /home/oracle/dump/full_01.dmp    bytes written: 8,357,294,080  Dump File: /home/oracle/dump/full_02.dmp    bytes written: 4,096  Worker 1 Status:  Process Name: DW00  State: EXECUTING                        Object Type: DATABASE_EXPORT/SCHEMA/TABLE/COMMENT  Completed Objects: 5,120  Worker Parallelism: 1  Worker 2 Status:  Process Name: DW01  State: EXECUTING                        Object Schema: P95169  Object Name: GRADE_RCCASE  Object Type: DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA  Completed Objects: 3  Total Objects: 1,866  Completed Rows: 23,505,613  Worker Parallelism: 1

2. parallel：动态调整并行度

Export> parallel=4

3. add_file：增加dumpfile

Export> ADD_FILE=hr2.dmp, dpump_dir2:hr3.dmp

4. stop_job, kill_job, start_job

stop_job只是暂停，之后可以用start_job重新启动，而kill_job直接杀掉，不可恢复

5. continue_client：退出交互模式，进入logging模式；

exit_client: 退出客户端