資料泵匯入分割槽表長時間HANG住
客戶10.2.0.3環境資料庫在使用資料庫匯入分割槽表時，出現長時間HANG住的情況。
透過10046跟蹤，發現等待發生在空閒等待wait for unread message on broadcast channel上，部分10046 TRACE摘錄如下：
=====================
PARSING IN CURSOR #27 len=93 dep=2 uid=0 ct=3 lid=0 tim=1314162845109698 hv=3190910778
ad='5a938130'
SELECT NVL(SUM(data_io),0) FROM
"SYS"."SEASHELL_ENTRY_P_20110126_88" WHERE process_order =
:1
END OF STMT
PARSE #27:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162845109693
BINDS #27:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00
scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00
siz=24 ff=0
kxsbbbfp=2acd522c5888 bln=22
avl=03 flg=05
value=-42
EXEC #27:c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162845109900
FETCH #27:c=999,e=91,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=1,tim=1314162845110019
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162845110264
hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162845110259
BINDS #36:
kkscoacd
Bind#0
oacdty=121 mxl=4000(4000) mxlc=00
mal=00 scl=00 pre=00
oacflg=00 fl2=206001 frm=00 csi=00
siz=4000 ff=0
kxsbbbfp=2acd51ff8e30 bln=4000
avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00
scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00
siz=24 ff=0
kxsbbbfp=2acd522c5888 bln=22
avl=02 flg=05
value=5
WAIT #36: nam='wait for unread message on broadcast channel' ela= 976833
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162846087749
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977485
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162847065320
*** 2012-08-23 14:19:16.395
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977438
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162848042834
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977564 channel
context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162849020507
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977425
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162849997982
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977515
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162850975546
EXEC #36:c=0,e=5865418,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162850975764
ERROR #36:err=25228 tim=805377243
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162850975992
hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=60,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162850975987
BINDS #36:
kkscoacd
Bind#0
oacdty=121 mxl=4000(4000) mxlc=00
mal=00 scl=00 pre=00
oacflg=00 fl2=206001 frm=00 csi=00
siz=4000 ff=0
kxsbbbfp=2acd51ff8e30 bln=4000
avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00
pre=00
oacflg=03 fl2=206001 frm=00 csi=00
siz=24 ff=0
kxsbbbfp=2acd522c5888 bln=22
avl=02 flg=05
value=5
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977489
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162851954175
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977604
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162852931835
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977468
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162853909360
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977445
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136 tim=1314162854886930
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977462
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162855864477
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977665
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162856842219
EXEC
#36:c=1000,e=5866346,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162856842423
ERROR #36:err=25228 tim=805377844
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162856842660
hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162856842654
BINDS #36:
kkscoacd
Bind#0
oacdty=121 mxl=4000(4000) mxlc=00
mal=00 scl=00 pre=00
oacflg=00 fl2=206001 frm=00 csi=00
siz=4000 ff=0
kxsbbbfp=2acd51ff8e30 bln=4000
avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00
scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00
siz=24 ff=0
kxsbbbfp=2acd522c5888 bln=22
avl=02 flg=05
value=5
*** 2012-08-23 14:19:26.408
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977414
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162857820783
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977354
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162858798202
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977523
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162859775833
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977339
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162860753273
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977629
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162861730982
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977450
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162862708486
EXEC
#36:c=1000,e=5865927,p=0,cr=4,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162862708674
ERROR #36:err=25228 tim=805378444
=====================
PARSING IN CURSOR #36 len=46 dep=2 uid=0 ct=47 lid=0 tim=1314162862708909
hv=420667605 ad='594460a0'
BEGIN :1 := sys.kupc$que_int.receive(:2); END;
END OF STMT
PARSE #36:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=1,tim=1314162862708903
BINDS #36:
kkscoacd
Bind#0
oacdty=121 mxl=4000(4000) mxlc=00
mal=00 scl=00 pre=00
oacflg=00 fl2=206001 frm=00 csi=00
siz=4000 ff=0
kxsbbbfp=2acd51ff8e30 bln=4000
avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00
scl=00 pre=00
oacflg=03 fl2=206001 frm=00 csi=00
siz=24 ff=0
kxsbbbfp=2acd522c5888 bln=22
avl=02 flg=05
value=5
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977366
channel context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162863686994
WAIT #36: nam='wait for unread message on broadcast channel' ela= 977565 channel
context=70113644704 channel handle=70314944608 p3=0 obj#=238136
tim=1314162864664610
顯然這種空閒等待的出現是不正常的，根據等待時間和等待的具體PL/SQL語句，可以確認為Bug 7439689 - impdp worker process may spin [ID 7439689.8]。這個bug影響的版本為10.2.0.4正是當前的版本，在10.2.0.5中解決了這個問題。此外在Solaris和HP-UX環境下，還有專門針對這個bug的補丁程式。
Oracle給出的臨時解決方案是用exp/imp代替資料泵。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-746401/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵匯入分割槽表統計資訊報錯（七）
- 資料泵匯入分割槽表統計資訊報錯（二）
- 資料泵匯入分割槽表統計資訊報錯（四）
- 資料泵匯入分割槽表統計資訊報錯（三）
- 資料泵匯入分割槽表統計資訊報錯（六）
- 資料泵匯入分割槽表統計資訊報錯（五）
- 使用PARTITION_OPTIONS引數控制資料泵分割槽表匯入
- 分割槽表匯入資料庫資料庫
- 匯入匯出 Oracle 分割槽表資料Oracle
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- 19c資料庫impdp匯入view時hang住資料庫View
- 資料泵匯出匯入表
- 分割槽表入無分割槽的資料庫資料庫
- Oracle使用資料泵匯出匯入表Oracle
- Oracle使用資料泵在異機之間匯出匯入表Oracle
- 使用expdp匯出分割槽表中的部分分割槽資料
- 資料泵匯出匯入
- 導數時資料庫hang住分析資料庫
- 【實驗】【PARTITION】exp匯出分割槽表資料
- Impdp資料泵匯入
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 資料泵的匯入匯出
- Oracle資料泵－schema匯入匯出Oracle
- 匯入excel 資料時間Excel
- 使用資料泵impdp匯入資料
- 【MySQL】innobackupex長時間hangMySql
- 【MySQL】innobackupex 長時間hangMySql
- 資料泵匯出匯入資料標準文件
- Oracle 28.6資料庫impdp匯入view時hang@11Oracle資料庫View
- Oracle資料泵的匯入和匯出Oracle
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 資料泵取匯出和匯入（一）
- 資料泵無法匯入JOB
- Oracle 12cr2 資料庫之間傳輸表，分割槽或子分割槽Oracle資料庫
- 自動備份、截斷分割槽表分割槽資料
- 大表裡有資料時表分割槽參考指令碼指令碼
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 11g後設資料匯入19c分割槽表建立不成功