how to stop oracle expdp

env: Oracle Linux 7
        Oracle 12c

當我們執行expdp產生檔案過程中，可能因為某些因素需要取消作業，很習慣直接Ctl + c取消指令，但是這可能造成session表面中斷了，但是實際上在背景卻還在執行中。
這會導致重新執行指令會出現錯誤，因此要用標準取消指令的方式進行。

1.可以用指令查詢datapump job執行狀況
command:
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
 

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;

 

OWNER_NAME

--------------------------------------------------------------------------------

JOB_NAME

--------------------------------------------------------------------------------

OPERATION

--------------------------------------------------------------------------------

JOB_MODE

--------------------------------------------------------------------------------

STATE

------------------------------

DBM

SYS_EXPORT_TABLE_01

EXPORT

 

OWNER_NAME

--------------------------------------------------------------------------------

JOB_NAME

--------------------------------------------------------------------------------

OPERATION

--------------------------------------------------------------------------------

JOB_MODE

--------------------------------------------------------------------------------

STATE

------------------------------

TABLE

EXECUTING

 

 

OWNER_NAME

--------------------------------------------------------------------------------

JOB_NAME

--------------------------------------------------------------------------------

OPERATION

--------------------------------------------------------------------------------

JOB_MODE

--------------------------------------------------------------------------------

STATE

------------------------------

DBM

SYS_EXPORT_TABLE_02

EXPORT

 

OWNER_NAME

--------------------------------------------------------------------------------

JOB_NAME

--------------------------------------------------------------------------------

OPERATION

--------------------------------------------------------------------------------

JOB_MODE

--------------------------------------------------------------------------------

STATE

------------------------------

TABLE

EXECUTING

 

 

OWNER_NAME

--------------------------------------------------------------------------------

JOB_NAME

--------------------------------------------------------------------------------

OPERATION

--------------------------------------------------------------------------------

JOB_MODE

--------------------------------------------------------------------------------

STATE

------------------------------

DBM

SYS_EXPORT_TABLE_03

EXPORT

 

OWNER_NAME

--------------------------------------------------------------------------------

JOB_NAME

--------------------------------------------------------------------------------

OPERATION

--------------------------------------------------------------------------------

JOB_MODE

--------------------------------------------------------------------------------

STATE

------------------------------

TABLE

 

EXECUTING

2.執行expdp指令，並指定table : SYS_EXPORT_TABLE_02，並執行暫停止datapump指令
command1:
expdp dbm/dbm@TESTPDB ATTACH=SYS_EXPORT_TABLE_02

command2:
STOP_JOB=IMMEDIATE
 

[oracle@TEST admin]$ expdp dbm/dbm@TESTPDB ATTACH=SYS_EXPORT_TABLE_02

 

Export: Release 12.1.0.2.0 - Production on Sun Oct 15 13:37:28 2017

 

Copyright (c) 1982, 2015, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

 

Job: SYS_EXPORT_TABLE_02

  Owner: DBM

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: 5B8EF07395B21537E0536E52C40A8AE6

  Start Time: Sunday, 15 October, 2017 12:01:06

  Mode: TABLE

  Instance: TEST

  Max Parallelism: 1

  Timezone: -07:00

  Timezone version: 18

  Endianness: LITTLE

  NLS character set: AL32UTF8

  NLS NCHAR character set: AL16UTF16

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        dbm/********@TESTPDB tables=SP65647288_DBFS.SHARE CONTENT=DATA_ONLY directory=soadmp dumpfile=soalob.dmp logfile=soalob.log

     INCLUDE_METADATA      0

  State: EXECUTING

  Bytes Processed: 0

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /u03/app/oracle/fast_recovery_area/TEST/soalob.dmp

    bytes written: 4,096

 

Worker 1 Status:

  Instance ID: 1

  Instance name: TEST

  Host name: TEST

  Process Name: DW01

  State: EXECUTING

  Object Schema: SP65647288_DBFS

  Object Name: SHARE

  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 1

  Total Objects: 1

  Completed Rows: 3,702,350

  Worker Parallelism: 1

 

Export> STOP_JOB=IMMEDIATE

 

Are you sure you wish to stop this job ([yes]/no): yes

3.找到系統物件SYS_EXPORT_TABLE_02，並且移除
command1:

SELECT o.status, o.object_id, o.object_type,o.owner||'.'||object_name "OWNER.OBJECT"

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name

AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

command2:
drop table DBM.SYS_EXPORT_TABLE_02;

SQL> SELECT o.status, o.object_id, o.object_type,o.owner||'.'||object_name "OWNER.OBJECT"

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name

AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;  2    3    4

 

STATUS   OBJECT_ID OBJECT_TYPE

------- ---------- -----------------------

OWNER.OBJECT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

VALID       105173 TABLE

DBM.SYS_EXPORT_TABLE_02

VALID       105195 TABLE

DBM.SYS_EXPORT_TABLE_03

 

SQL> drop table DBM.SYS_EXPORT_TABLE_02;

Table dropped.

SQL>