expdp导出卡住问题 - 墨天轮文档

expdp导出卡住问题

A琳琳

2242

2页

6次

2020-04-29

5墨值下载

expdp 导出卡住问题诊断

[oracle@database ~]$nohup expdp gg/gg directory=gg_DB dumpfile=gg_20171212_

%u.dmp logfile=gg_zc_20171212.log parallel=4 job_name=exp_gg_20171212

filesize=20g cluster=no compression=DATA_ONLY &

...................................................

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

导出一直卡在这里。

1.刚开始以为是 ESTIMATE 的问题，ESTIMATE 默认是 BLOCKS 。

ESTIMATE 计算作业估计值。

有效的关键字值为: [BLOCKS] 和 STATISTICS。

于是把调整了一下，ESTIMATE=STATISTICS，结果还是卡住。

2.回归这个问题，那就要找到它到底在做什么，怎么看呢？

select s.EVENT,s.MODULE,s.PROGRAM from gv$session s where s.PROGRAM like '%(DW

%';(也可以查 MODULE=Data Pump Worker)

看到了有行锁 enq: TX - contention,

3.查下谁堵塞了它，kill session，可以导出了。

SELECT bs.INST_ID,

bs.username "Blocking User",

bs.username "DB User",

bs.SID "SID",

bs.serial# "Serial#",

bs.sql_address "address",

bs.sql_hash_value "Sql hash",

bs.program "Blocking App",

bs.machine "Blocking Machine",

bs.osuser "Blocking OS User",

bs.serial# "Serial#",

ws.username "Waiting User",

ws.SID "WSID",

ws.program "Waiting App",

ws.machine "Waiting Machine",

ws.osuser "Waiting OS User",

ws.serial# "WSerial#",

wk.TYPE lock_type,

hk.lmode mode_held,

wk.request mode_requested,

TO_CHAR(hk.id1) lock_id1,

TO_CHAR(hk.id2) lock_id2,

hk.BLOCK blocking_others

FROM gv$lock hk, gv$session bs, gv$lock wk, gv$session ws

WHERE hk.BLOCK = 1

AND hk.lmode != 0

AND hk.lmode != 1

AND wk.request != 0

AND wk.TYPE(+) = hk.TYPE

AND wk.id1(+) = hk.id1

AND wk.id2(+) = hk.id2

AND hk.SID = bs.SID(+)

AND wk.SID = ws.SID(+)

AND (bs.username IS NOT NULL)

AND (bs.username <> 'SYSTEM')

AND (bs.username <> 'SYS')

ORDER BY 1;

of 2

5墨值下载

oracle 安装部署

关注

评论