expdp/impdp 任务终止不能靠Ctrl+C

背景

记录一次EXPDP备份时的经验总结。当使用expdp备份的时候评估出备份目录的空间不足时，直接ctrl+c“停掉”，此时的“停掉”并不是真正的任务终止，它还在后台运行。现在总结一下具体的停掉流程。

Ctrl+c 取消操作

[oracle@localhost~]$  expdp two/two directory=dp_dir  dumpfile=2023_08_20_two.dmp   
......
Starting "TWO"."SYS_EXPORT_SCHEMA_03":  two/******** directory=dp_dir dumpfile=2023_08_20_two.dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
^C
Export> 

查看运行状态

SQL> select job_name,state from dba_datapump_jobs; 

JOB_NAME                       STATE
------------------------------ ------------------------------
SYS_EXPORT_SCHEMA_03           EXECUTING

SYS_EXPORT_SCHEMA_03 任务还在运行：EXECUTING

终止操作 ： stop_job

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

[oracle@localhost~]$ 

– 再查状态：

SQL> select job_name,state from dba_datapump_jobs; 
JOB_NAME                       STATE
------------------------------ ------------------------------
SYS_EXPORT_SCHEMA_03           STOP PENDING

删除任务： kill_job

-- 重新attach 到该job里 
 [oracle@localhost~]$ expdp two/two attach=SYS_EXPORT_SCHEMA_03             
......

Job: SYS_EXPORT_SCHEMA_03
  Owner: TWO                            
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: 0355E1FC2C5463A4E06359050A0AD071
  Start Time: Sunday, 20 August, 2023 14:29:29
  Mode: SCHEMA                         
  Instance: pay1
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        two/密码 directory=dp_dir dumpfile=2023_08_20_two.dmp 
  State: STOP PENDING                   
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /home/oracle/expdb_back/2023_08_20_two.dmp
    bytes written: 7,778,304
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: TWO
  Object Name: TWO_TEST
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 4,063
  Completed Rows: 1,932,502
  Worker Parallelism: 1
-- 删除任务
Export>  kill_job
Are you sure you wish to stop this job ([yes]/no): yes
-- 再查任务：
SQL> select job_name,state from dba_datapump_jobs; 
no rows selected

至此expdp 任务才算终止。

清理任务产生的外部表

1、查看EXTERNAL TABLE
expdp和impdp中使用了外部表（使用了directory的一般都会用到外部表）

SELECT OWNER,
       OBJECT_NAME,
       OBJECT_TYPE,
       status,
       TO_CHAR (CREATED, 'dd-mon-yyyy hh24:mi:ss') created,
       TO_CHAR (LAST_DDL_TIME, 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
  FROM dba_objects
 WHERE object_name LIKE 'ET$%';
--------------------------------------------------------------------------------
 SELECT owner,
         TABLE_NAME,
         DEFAULT_DIRECTORY_NAME,
         ACCESS_TYPE
    FROM dba_external_tables
   WHERE owner NOT IN ('SYS')
ORDER BY 1, 2;

2、查看MASTER TABLE

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;

3、.删除临时外部表（第1步的结果）
SQL> drop table F3602914.ET$0005DEFF0002 purge;
4.删除主表（第2步结果）
SQL> drop table TWO.SYS_EXPORT_SCHEMA_02 purge;
5、登录异常

 expdp two/twoattach=SYS_IMPORT_TABLE_02 
......
Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/expdb_back/2021-11-04-TEST.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

– 文件已经删除，任务已终止

SQL> select OWNER_NAME,JOB_NAME ,STATE from dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       STATE
------------------------------ ------------------------------ ------------------
TWO                            SYS_IMPORT_TABLE_02            NOT RUNNING
SQL> drop table pay. SYS_IMPORT_TABLE_02 purge;
Table dropped.
SQL> select OWNER_NAME,JOB_NAME ,STATE from dba_datapump_jobs;
no rows selected