Oracle数据泵自动删除,Oracle 数据泵终止任务

当我们在生产环境下，不小心触发了逻辑备份(expdp)，可能会造成IO阻塞，影响生产业务

那么如何终止expdp导出任务呢

**手动执行全库导出脚本：**

[oracle@server1 ~]$ ./expdp.sh

**查看进程信息：**

[root@server1 ~]# ps -ef | grep expdp

oracle 3233 2842 0 04:58 pts/1 00:00:00 /bin/bash ./expdp.sh

oracle 3247 3233 0 04:58 pts/1 00:00:00 expdp sysdba' directory=dump_file dumpfile=prod_20200803.dmp log=prod_20200803.log full=y compression=ALL

root 3254 3081 0 04:58 pts/3 00:00:00 grep expdp

[root@server1 ~]# kill -9 3247

./expdp.sh: line 13: 3247 Killed expdp \'/as sysdba\' directory=dump_file dumpfile=$FILE_NAME log=$FILE_LOG full=y compression=ALL

**数据量还是在增长：(kill发送信号量无法终止expdp导出任务)**

[oracle@server1 backup]$ du -sh *

1.2Gprod

48Mprod_20200803.dmp

104Kprod_20200803.log

[oracle@server1 backup]$ du -sh *

1.2Gprod

50Mprod_20200803.dmp

104Kprod_20200803.log

[oracle@server1 backup]$ du -sh *

1.2Gprod

54Mprod_20200803.dmp

108Kprod_20200803.log

**查询数据泵作业：**

SQL> select job_name,state,owner_name from dba_datapump_jobs;

JOB_NAME STATE OWNER_NAME

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

SYS_EXPORT_FULL_01 EXECUTING SYS

[oracle@server1 ~]$ expdp \'/ as sysdba\' attach=**SYS_EXPORT_FULL_01**

Export: Release 11.2.0.4.0 - Production on Mon Aug 3 05:03:13 2020

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01

Owner: SYS

Operation: EXPORT

Creator Privs: TRUE

GUID: ABEC566151380D0DE05379C7A8C00DBB

Start Time: Monday, 03 August, 2020 5:02:18

Mode: FULL

Instance: prod

Max Parallelism: 1

EXPORT Job Parameters:

Parameter Name Parameter Value:

CLIENT_COMMAND "/******** AS SYSDBA" directory=dump_file dumpfile=prod_20200803.dmp logfile=prod_20200803.log full=y reuse_dumpfiles=true

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /backup/prod_20200803.dmp

bytes written: 4,096

Worker 1 Status:

Process Name: DW00

State: EXECUTING

Object Schema: APEX_030200

Object Name: WWV_FLOW_FL_CH_IDX

Object Type: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX

Completed Objects: 1,466

Worker Parallelism: 1

**Export> status**

Job: SYS_EXPORT_FULL_01

Operation: EXPORT

Mode: FULL

State: EXECUTING

Bytes Processed: 0

Current Parallelism: 1

Job Error Count: 0

Dump File: /backup/prod_20200803.dmp

bytes written: 4,096

Worker 1 Status:

Process Name: DW00

State: EXECUTING

Object Type: DATABASE_EXPORT/SCHEMA/VIEW/COMMENT

Completed Objects: 2,198

Total Objects: 2,198

Worker Parallelism: 1

**停止数据泵作业：**

**Export> stop_job** ** //或者 kill_job 该种方式会永久终止任务，并自动删除产生的状态表**

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

**再次查看状态表：**

SQL> select job_name,state,owner_name from dba_datapump_jobs;

JOB_NAME STATE OWNER_NAME

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

SYS_EXPORT_FULL_01 ** NOT RUNNING** SYS

**删除状态表：**

SQL>drop table SYS_EXPORT_FULL_01 purge;

Table dropped.

**当状态表过多时，采用拼接删除语句：**

SQL> select 'drop ' || 'table ' || job_name || ' purge' from dba_datapump_jobs;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69975956/viewspace-2708711/，如需转载，请注明出处，否则将追究法律责任。