查询数据泵进程号
select a.sid ,c.pid,a.event,b.SESSION_TYPE from v$session a ,dba_datapump_sessions b ,v$process c
where a.saddr = b.saddr
and a.paddr = c.addr;

查询数据泵master表
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;

正常停止数据泵进程
SET serveroutput on 
SET lines 100 
DECLARE 
h1 NUMBER; 
BEGIN 
h1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_TABLE_01','SCOTT'); 
DBMS_DATAPUMP.STOP_JOB (h1); 
END; 
/

oradebug setorapid 741 # 741 为pid
oradebug event 10046 trace name context level 12;
oradebug tracefile_name

查看trace
cat tracefile_name

查看具体对象
select OWNER,SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID from dba_extents where FILE_ID=1926 and BLOCK_ID=67911;
SQL> select object_name , object_type from dba_objects where object_id = 14;

-- 查看等待事件
SQL> select event ,sum(wait_time), count(1) from v$session where wait_class<> 'Idle' and sid=1438 group by event order by 2 desc ,3 desc ;
EVENT                                                            SUM(WAIT_TIME)   COUNT(1)
---------------------------------------------------------------- -------------- ----------
db file sequential read                                                       0          1

SQL> select a.sid ,a.event,c.pid,b.session_type from v$session a ,dba_datapump_sessions b ,v$process c where a.saddr = b.saddr and a.paddr = c.addr and  sid=1292;
       SID EVENT                                                                   PID SESSION_TYPE
---------- ---------------------------------------------------------------- ---------- --------------
      1292 wait for unread message on broadcast channel          666    DBMS_DATAPUMP

补充：查看数据泵进程的工作状态

1. set line 9999
2. col owner_name for a10
3. col job_name for a25 
4. col operation for a10
5. col job_mode for a10 
6. col state for a15 
7. col job_mode for a10 
8. col state for a15 
9. col osuser for a10
10. col "degree|attached|datapump" for a25
11. col session_info for a20 
12. SELECT s.inst_id,
13.        dj.owner_name,
14.        dj.job_name,
15.        dj.operation,
16.        dj.job_mode,
17.        dj.state,
18.        dj.degree || ',' || dj.attached_sessions || ',' ||
19.        dj.datapump_sessions "degree|attached|datapump",
20.        ds.session_type,
21.        s.osuser,
22.        (SELECT s.SID || ',' || s.SERIAL# || ',' || p.SPID
23.           FROM gv$process p
24.          where s.paddr = p.addr
25.            AND s.inst_id = p.inst_id) session_info
26.   FROM DBA_DATAPUMP_JOBS dj --gv$datapump_job 
27.   full outer join dba_datapump_sessions ds --gv$datapump_session
28.     on (dj.job_name = ds.job_name and dj.owner_name = ds.owner_name)
29.   left outer join gv$session s
30.     on (s.saddr = ds.saddr)
31.  ORDER BY dj.owner_name, dj.job_name;

INST_ID OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE degree|attached|datapump SESSION_TYPE OSUSER SESSION_INFO
---------- ---------- ------------------------- ---------- ---------- --------------- ------------------------- -------------- ---------- --------------------
1 SYS SYS_IMPORT_TABLE_01 IMPORT TABLE EXECUTING 1,1,3 DBMS_DATAPUMP oracle 69,29083,122807
1 SYS SYS_IMPORT_TABLE_01 IMPORT TABLE EXECUTING 1,1,3 WORKER oracle 884,18069,123122
1 SYS SYS_IMPORT_TABLE_01 IMPORT TABLE EXECUTING 1,1,3 MASTER oracle 203,15283,122983