-- 确定正在执行DataPump导出的会话:

select vs.sid, vp.program PROCESSNAME, vp.spid THREADID

from v$session vs,

v$process vp ,

dba_datapump_sessions dp

where vp.addr = vs.paddr(+) and

vs.saddr = dp.saddr;

SID PROCESSNAME THREADID

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

150 oracle (TNS V1-V3) 18132

148 oracle (DM00) 18136

195 oracle (DW01) 18138

-- 检查v$session_wait视图以验证等待事件

select sid,event,p1,p2,p3, seconds_in_wait

from v$session_wait

where sid in (138, 140, 145);

SID EVENT P1 P2 P3 SECONDS_IN_WAIT

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

148 wait for unread message on broadcast channel 1562798 1562792 52663 291

150 wait for unread message on broadcast channel 1562798 1562792 0 597

195 enq: TM - contention 1414339 1414323 0 597 <= waiting session

-- 检查 gv$lock 视图以验证锁定问题.

select * from gv$lock;

INST_ID ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

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

1 5CE7352C 5CE73544 144 TM 52663 0 6 0 924 1 <= This session has a lock on object 52663.

1 5CE735D8 5CE735F0 145 TM 52663 0 0 2 879 0 <= same scenario

1 5D20E1C0 5D20E1D4 195 TO 7133 1 3 0 892 0

-- 检查获取的对象ID对应的对象名称

select owner, object_name

from dba_objects

where object_id = 52663;

OWNER OBJECT_NAME

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

SCOTT emp

-- 为进程17118生成错误堆栈，以检查DataPump进程的当前SQL语句

SQL> oradebug setospid 17118 >>equates to sid=195 from above<<

SQL> oradebug dump errorstack 3

SQL> oradebug tracefile_name <== To get the trace name

-- 当前SQL语句

LOCK TABLE "SCOTT"."EMP" IN ROW SHARE MODE

问题出现的原因是会话144对表emp 持有锁定，而DataPump进程无法获取该表的锁定。