Expdp Hangs 排查

-- 确定正在执行DataPump导出的会话:select vs.sid, vp.program PROCESSNAME, vp.spid THREADIDfrom v$session vs,v$process vp ,dba_datapump_sessions dpwhere vp.addr = vs.paddr(+) andvs.saddr = dp.saddr;SID PROCESSNAME THREADID--- ------------------- ---------------------150 oracle (TNS V1-V3) 18132148 oracle (DM00) 18136195 oracle (DW01) 18138-- 检查v$session_wait视图以验证等待事件select sid,event,p1,p2,p3, seconds_in_waitfrom v$session_waitwhere sid in (138, 140, 145);SID EVENT P1 P2 P3 SECONDS_IN_WAIT--- -------------------------------------------- ------- ------- ----- ---------------148 wait for unread message on broadcast channel 1562798 1562792 52663 291150 wait for unread message on broadcast channel 1562798 1562792 0 597195 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 scenario1 5D20E1C0 5D20E1D4 195 TO 7133 1 3 0 892 0-- 检查获取的对象ID对应的对象名称select owner, object_namefrom dba_objectswhere 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 3SQL> oradebug tracefile_name <== To get the trace name-- 当前SQL语句LOCK TABLE "SCOTT"."EMP" IN ROW SHARE MODE问题出现的原因是会话144对表emp 持有锁定，而DataPump进程无法获取该表的锁定。