用hanganalyze解决row cache lock（转贴）

今天某省的一位同事来说，在执行一个split分区的脚本时长时间没有响应。登录上去查看，手工执行了split脚本，发现确实会hang住：

SQL> l
  1  ALTER TABLE A_PDA_SP_STAT SPLIT PARTITION P_MAX AT (20090609)
  2   INTO (PARTITION P_20090608 TABLESPACE TS_DATA_A
  3*  , PARTITION P_MAX TABLESPACE TS_DATA_A)
SQL>

检查该session的等待事件：

EVENT                                  P1         P2         P3
---------------------------- -- ---------- ---------- ----------
row cache lock                          8          0          5

查了网上的一些资料，说和sga的shared pool大小不足有关，或者和sequence的cache不大有关。经过分析，这2个原因应该都不是。因为1、如果是shared pool不足，这样的现象一般是某个sql执行的比较慢，但是还是会执行完，而不是像现在这样的挂住；2，只是执行split分区，并没有和sequence相关。

在这里，我们用hanganalyze来进行分析。

hanganalyze用法：

(在这里我们用oradebug来用做hanganalyze)
1.oradebug setospid
2.oradebug analyze <level>
然后观察出来的trace文件。

这里的level有如下几级：

Dump     Level Dump Contains
----------------------------------------
1-2      Only HANGANALYZE output, no process dump at all
3        Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
4        Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
5        Level 4 + Dump all processes involved in wait chains (NLEAF state)
10       Dump all processes (IGN state)

我们现在来看看出来的trace文件：

SQL> select spid from v$session a,v$process b where a.paddr=b.addr and a.sid=295;
 
SPID
------------
19237
 
SQL> oradebug SETOSPID 19237
Oracle pid: 235, Unix process pid: 19237, image: oracle@hl_rdb01 (TNS V1-V3)
SQL>
SQL> oradebug hanganalyze 3;
Cycle 1: (0/295)
Cycle 2: (0/254)--(0/239)
Hang Analysis in /oracle/app/oracle/admin/hlreport/udump/hlreport_ora_25247.trc
SQL> !
$ more /oracle/app/oracle/admin/hlreport/udump/hlreport_ora_25247.trc
Dump file /oracle/app/oracle/admin/hlreport/udump/hlreport_ora_25247.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/9.2.0
System name:    HP-UX
Node name:      hl_rdb01
Release:        B.11.11
Version:        U
Machine:        9000/800
Instance name: hlreport
Redo thread mounted by this instance: 1
Oracle process number: 157
Unix process pid: 25247, image: oracle@hl_rdb01 (TNS V1-V3)
 
*** SESSION ID:(312.10459) 2009-05-20 16:21:58.423
*** 2009-05-20 16:21:58.423
==============
HANG ANALYSIS:
==============
 
Cycle 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/329/43816/0x4d6b5638/23487/row cache lock>
 -- <0/254/19761/0x4d687438/23307/library cache lock>
Cycle 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/295/57125/0x4d6b8978/19237/row cache lock>
Cycle 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/295/57125/0x4d6b8978/19237/row cache lock>
Open chains found:
Other chains found:
Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/312/10459/0x4d69f9b8/25247/No Wait>
Extra information that will be dumped at higher levels:
[level  3] :   4 node dumps -- [IN_HANG]
[level  5] :   1 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP]
[level 10] : 223 node dumps -- [IGN]
 
State of nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor):
[0]/0/1/1/0x4d7146c0/5132/IGN/1/2//none
[1]/0/2/1/0x4d715150/5134/IGN/3/4//none
[2]/0/3/1/0x4d715be0/5136/IGN/5/6//none
[3]/0/4/1/0x4d716670/5138/IGN/7/8//none
[4]/0/5/1/0x4d717100/5140/IGN/9/10//none
[5]/0/6/1/0x4d717b90/5142/IGN/11/12//none
[6]/0/7/1/0x4d718620/5144/IGN/13/14//none
[7]/0/8/1406/0x4d7190b0/15097/IGN/15/16//none
[8]/0/9/777/0x4d719b40/8987/IGN/17/18//none
[9]/0/10/27393/0x4d71a5d0/14344/IGN/19/20//none
[11]/0/12/55791/0x4d71baf0/5045/IGN/21/22//none
[12]/0/13/887/0x4d71c580/9001/IGN/23/24//none
[13]/0/14/43813/0x4d71d010/5043/IGN/25/26//none
[15]/0/16/47587/0x4d71e530/15101/IGN/27/28//none
[16]/0/17/31389/0x4d71efc0/14569/IGN/29/30//none
[19]/0/20/907/0x4d720f70/8985/IGN/31/32//none
[20]/0/21/982/0x4d721a00/9015/IGN/33/34//none
[21]/0/22/38358/0x4d722490/14219/IGN/35/36//none
[22]/0/23/1300/0x4d722f20/8989/IGN/37/38//none
[23]/0/24/1092/0x4d7239b0/8991/IGN/39/40//none
[24]/0/25/852/0x4d724440/8993/IGN/41/42//none
[25]/0/26/2289/0x4d724ed0/14629/IGN/43/44//none
[26]/0/27/53264/0x4d725960/14490/IGN/45/46//none
[28]/0/29/296/0x4d726e80/9019/IGN/47/48//none
[30]/0/31/38435/0x4d7283a0/15119/IGN/49/50//none
[31]/0/32/60/0x4d728e30/9017/IGN/51/52//none
[33]/0/34/147/0x4d72a350/9007/IGN/53/54//none
[34]/0/35/38678/0x4d72ade0/14484/IGN/55/56//none
[35]/0/36/206/0x4d72b870/8995/IGN/57/58//none
[36]/0/37/657/0x4d72c300/8999/IGN/59/60//none
[37]/0/38/35/0x4d72cd90/9009/IGN/61/62//none
[38]/0/39/70/0x4d72d820/9025/IGN/63/64//none
[39]/0/40/34/0x4d72e2b0/9021/IGN/65/66//none
[40]/0/41/30/0x4d72ed40/9029/IGN/67/68//none
[41]/0/42/168/0x4d72f7d0/8997/IGN/69/70//none
[42]/0/43/74/0x4d730260/9005/IGN/71/72//none
[43]/0/44/97/0x4d730cf0/9003/IGN/73/74//none
[44]/0/45/41/0x4d731780/9011/IGN/75/76//none
[45]/0/46/10/0x4d732210/9031/IGN/77/78//none
[46]/0/47/111/0x4d732ca0/9013/IGN/79/80//none
[47]/0/48/29/0x4d733730/9023/IGN/81/82//none
[48]/0/49/562/0x4d7341c0/9155/IGN/83/84//none
[49]/0/50/35/0x4d734c50/9027/IGN/85/86//none
[50]/0/51/98/0x4d7356e0/9157/IGN/87/88//none
[51]/0/52/32/0x4d736170/9153/IGN/89/90//none
[52]/0/53/5/0x4d736c00/9159/IGN/91/92//none
[53]/0/54/2/0x4d737690/9161/IGN/93/94//none
[54]/0/55/2/0x4d738120/9163/IGN/95/96//none
[55]/0/56/2/0x4d738bb0/9165/IGN/97/98//none
[56]/0/57/2/0x4d739640/9167/IGN/99/100//none
[57]/0/58/2/0x4d73a0d0/9169/IGN/101/102//none
[58]/0/59/2/0x4d73ab60/9171/IGN/103/104//none
[59]/0/60/5/0x4d73b5f0/9173/IGN/105/106//none
[60]/0/61/2/0x4d73c080/9175/IGN/107/108//none
[61]/0/62/2/0x4d73cb10/9177/IGN/109/110//none
[62]/0/63/2/0x4d73d5a0/9179/IGN/111/112//none
[63]/0/64/2/0x4d73e030/9181/IGN/113/114//none
[64]/0/65/2/0x4d73eac0/9183/IGN/115/116//none
[65]/0/66/2/0x4d73f550/9185/IGN/117/118//none
[66]/0/67/2/0x4d73ffe0/9187/IGN/119/120//none
[67]/0/68/3/0x4d740a70/9189/IGN/121/122//none
[68]/0/69/2/0x4d741500/9191/IGN/123/124//none
[69]/0/70/2/0x4d741f90/9193/IGN/125/126//none
[70]/0/71/2/0x4d742a20/9195/IGN/127/128//none
[71]/0/72/2/0x4d7434b0/9197/IGN/129/130//none
[72]/0/73/2/0x4d743f40/9199/IGN/131/132//none
[73]/0/74/46090/0x4d7449d0/14466/IGN/133/134//none
[74]/0/75/7975/0x4d745460/16055/IGN/135/136//none
[75]/0/76/58366/0x4d745ef0/14575/IGN/137/138//none
[76]/0/77/45495/0x4d746980/28191/IGN/139/140//none
[77]/0/78/22196/0x4d747410/28195/IGN/141/142//none
[79]/0/80/20933/0x4d748930/14418/IGN/143/144//none
[80]/0/81/23732/0x4d7493c0/14167/IGN/145/146//none
[81]/0/82/10477/0x4d749e50/15107/IGN/147/148//none
[82]/0/83/26093/0x4d74a8e0/14515/IGN/149/150//none
[83]/0/84/6001/0x4d74b370/14617/IGN/151/152//none
[84]/0/85/32015/0x4d74be00/14442/IGN/153/154//none
[85]/0/86/24327/0x4d74c890/15059/IGN/155/156//none
[86]/0/87/28299/0x4d74d320/14191/IGN/157/158//none
[87]/0/88/50091/0x4d74ddb0/14378/IGN/159/160//none
[88]/0/89/45814/0x4d74e840/27611/IGN/161/162//none
[89]/0/90/65028/0x4d74f2d0/14599/IGN/163/164//none
[90]/0/91/27967/0x4d74fd60/15876/IGN/165/166//none
[91]/0/92/37999/0x4d7507f0/14412/IGN/167/168//none
[93]/0/94/39252/0x4d751d10/15091/IGN/169/170//none
[94]/0/95/30893/0x4d7527a0/14611/IGN/171/172//none
[95]/0/96/23719/0x4d753230/8817/IGN/173/174//none
[96]/0/97/43200/0x4d753cc0/14508/IGN/175/176//none
[97]/0/98/31259/0x4d754750/14332/IGN/177/178//none
[98]/0/99/60179/0x4d7551e0/14932/IGN/179/180//none
[99]/0/100/13635/0x4d755c70/14296/IGN/181/182//none
[103]/0/104/35242/0x4d7586b0/14308/IGN/183/184//none
[104]/0/105/26724/0x4d759140/14478/IGN/185/186//none
[107]/0/108/13234/0x4d75b0f0/5632/IGN/187/188//none
[109]/0/110/45483/0x4d75c610/14545/IGN/189/190//none
[110]/0/111/56297/0x4d75d0a0/27613/IGN/191/192//none
[111]/0/112/23570/0x4d75db30/14677/IGN/193/194//none
[112]/0/113/13257/0x4d75e5c0/28189/IGN/195/196//none
[113]/0/114/511/0x4d75f050/14362/IGN/197/198//none
[114]/0/115/7143/0x4d75fae0/25127/IGN/199/200//none
[115]/0/116/29352/0x4d760570/14527/IGN/201/202//none
[117]/0/118/9558/0x4d761a90/15089/IGN/203/204//none
[118]/0/119/22506/0x4d762520/14472/IGN/205/206//none
[119]/0/120/39687/0x4d762fb0/7473/IGN/207/208//none
[121]/0/122/36574/0x4d7644d0/14695/IGN/209/210//none
[122]/0/123/159/0x4d764f60/14581/IGN/211/212//none
[123]/0/124/38743/0x4d7659f0/14350/IGN/213/214//none
[125]/0/126/107/0x4d766f10/14689/IGN/215/216//none
[127]/0/128/197/0x4d768430/14551/IGN/217/218//none
[128]/0/129/863/0x4d768ec0/15073/IGN/219/220//none
[129]/0/130/121/0x4d769950/15061/IGN/221/222//none
[130]/0/131/2293/0x4d76a3e0/14587/IGN/223/224//none
[131]/0/132/25524/0x4d76ae70/14356/IGN/225/226//none
[132]/0/133/32375/0x4d76b900/14454/IGN/227/228//none
[133]/0/134/201/0x4d76c390/14400/IGN/229/230//none
[134]/0/135/6178/0x4d76ce20/14338/IGN/231/232//none
[135]/0/136/246/0x4d76d8b0/14741/IGN/233/234//none
[136]/0/137/464/0x4d76e340/15099/IGN/235/236//none
[137]/0/138/150/0x4d76edd0/15057/IGN/237/238//none
[138]/0/139/22492/0x4d76f860/14227/IGN/239/240//none
[139]/0/140/24420/0x4d7702f0/15055/IGN/241/242//none
[140]/0/141/850/0x4d770d80/14368/IGN/243/244//none
[141]/0/142/34914/0x4d771810/14563/IGN/245/246//none
[143]/0/144/42745/0x4d772d30/15065/IGN/247/248//none
[147]/0/148/3961/0x4d775770/14735/IGN/249/250//none
[148]/0/149/5957/0x4d776200/15109/IGN/251/252//none
[149]/0/150/137/0x4d776c90/14920/IGN/253/254//none
[150]/0/151/208/0x4d777720/15085/IGN/255/256//none
[151]/0/152/247/0x4d7781b0/15067/IGN/257/258//none
[152]/0/153/275/0x4d778c40/14284/IGN/259/260//none
[153]/0/154/127/0x4d7796d0/14539/IGN/261/262//none
[154]/0/155/227/0x4d77a160/14926/IGN/263/264//none
[155]/0/156/105/0x4d77abf0/15077/IGN/265/266//none
[156]/0/157/149/0x4d77b680/15014/IGN/267/268//none
[157]/0/158/235/0x4d77c110/15121/IGN/269/270//none
[158]/0/159/189/0x4d77cba0/14665/IGN/271/272//none
[159]/0/160/206/0x4d77d630/14386/IGN/273/274//none
[161]/0/162/255/0x4d77eb50/15105/IGN/275/276//none
[163]/0/164/883/0x4d780070/15039/IGN/277/278//none
[164]/0/165/247/0x4d780b00/15063/IGN/279/280//none
[165]/0/166/114/0x4d781590/15079/IGN/281/282//none
[166]/0/167/145/0x4d782020/14683/IGN/283/284//none
[167]/0/168/124/0x4d782ab0/15075/IGN/285/286//none
[168]/0/169/198/0x4d783540/14502/IGN/287/288//none
[169]/0/170/140/0x4d783fd0/14641/IGN/289/290//none
[170]/0/171/123/0x4d784a60/14717/IGN/291/292//none
[171]/0/172/46378/0x4d7854f0/14747/IGN/293/294//none
[172]/0/173/231/0x4d785f80/15115/IGN/295/296//none
[173]/0/174/22599/0x4d786a10/14173/IGN/297/298//none
[174]/0/175/16432/0x4d7874a0/24107/IGN/299/300//none
[178]/0/179/1256/0x4d789ee0/14460/IGN/301/302//none
[179]/0/180/142/0x4d78a970/15087/IGN/303/304//none
[180]/0/181/35157/0x4d78b400/15008/IGN/305/306//none
[181]/0/182/14922/0x4d78be90/15043/IGN/307/308//none
[182]/0/183/53785/0x4d78c920/16526/IGN/309/310//none
[183]/0/184/864/0x4d78d3b0/15123/IGN/311/312//none
[184]/0/185/1304/0x4d78de40/14326/IGN/313/314//none
[185]/0/186/21577/0x4d78e8d0/14430/IGN/315/316//none
[187]/0/188/13421/0x4d78fdf0/14394/IGN/317/318//none
[188]/0/189/58340/0x4d790880/14271/IGN/319/320//none
[189]/0/190/18736/0x4d791310/14647/IGN/321/322//none
[190]/0/191/4653/0x4d791da0/14235/IGN/323/324//none
[191]/0/192/46642/0x4d792830/14290/IGN/325/326//none
[192]/0/193/18178/0x4d7932c0/15093/IGN/327/328//none
[194]/0/195/34657/0x4d7947e0/15069/IGN/329/330//none
[196]/0/197/28459/0x4d795d00/14448/IGN/331/332//none
[197]/0/198/8742/0x4d796790/14593/IGN/333/334//none
[199]/0/200/38023/0x4d797cb0/14424/IGN/335/336//none
[200]/0/201/24923/0x4d798740/14185/IGN/337/338//none
[201]/0/202/45711/0x4d7991d0/14723/IGN/339/340//none
[202]/0/203/28527/0x4d799c60/14245/IGN/341/342//none
[205]/0/206/551/0x4d79bc10/14711/IGN/343/344//none
[206]/0/207/44786/0x4d79c6a0/14701/IGN/345/346//none
[208]/0/209/455/0x4d79dbc0/15012/IGN/347/348//none
[210]/0/211/215/0x4d79f0e0/15117/IGN/349/350//none
[211]/0/212/159/0x4d79fb70/15051/IGN/351/352//none
[213]/0/214/58590/0x4d7a1090/14278/IGN/353/354//none
[214]/0/215/262/0x4d7a1b20/14521/IGN/355/356//none
[215]/0/216/6287/0x4d7a25b0/16605/IGN/357/358//none
[216]/0/217/145/0x4d7a3040/15113/IGN/359/360//none
[217]/0/218/631/0x4d7a3ad0/15041/IGN/361/362//none
[218]/0/219/1665/0x4d7a4560/14406/IGN/363/364//none
[219]/0/220/469/0x4d7a4ff0/14671/IGN/365/366//none
[220]/0/221/81/0x4d7a5a80/15071/IGN/367/368//none
[221]/0/222/225/0x4d7a6510/14659/IGN/369/370//none
[222]/0/223/89/0x4d7a6fa0/15081/IGN/371/372//none
[224]/0/225/133/0x4d7a84c0/14605/IGN/373/374//none
[225]/0/226/148/0x4d7a8f50/15095/IGN/375/376//none
[226]/0/227/514/0x4d7a99e0/25241/IGN/377/378//none
[227]/0/228/436/0x4d7aa470/14320/IGN/379/380//none
[228]/0/229/155/0x4d7aaf00/14635/IGN/381/382//none
[229]/0/230/795/0x4d7ab990/15045/IGN/383/384//none
[230]/0/231/664/0x4d7ac420/15035/IGN/385/386//none
[233]/0/234/127/0x4d7ae3d0/15037/IGN/387/388//none
[234]/0/235/3215/0x4d7aee60/14623/IGN/389/390//none
[236]/0/237/225/0x4d7b0380/14265/IGN/391/392//none
[237]/0/238/46137/0x4d7b0e10/14496/IGN/393/394//none
[238]/0/239/57618/0x4d7b18a0/13476/IN_HANG/395/402/[294][238][328][253]/none
[240]/0/241/51084/0x4d7b2dc0/14729/IGN/403/404//none
[241]/0/242/118/0x4d7b3850/14557/IGN/405/406//none
[243]/0/244/78/0x4d7b4d70/15083/IGN/407/408//none
[244]/0/245/41918/0x4d7b5800/14255/IGN/409/410//none
[245]/0/246/122/0x4d7b6290/15049/IGN/411/412//none
[247]/0/248/6353/0x4d7b77b0/15047/IGN/413/414//none
[248]/0/249/166/0x4d7b8240/14314/IGN/415/416//none
[250]/0/251/205/0x4d7b9760/14179/IGN/417/418//none
[253]/0/254/19761/0x4d7bb710/23307/IN_HANG/397/400/[328][238][294]/294
[256]/0/257/296/0x4d7bd6c0/14302/IGN/419/420//none
[257]/0/258/53847/0x4d7be150/14203/IGN/421/422//none
[260]/0/261/53933/0x4d7c0100/14533/IGN/423/424//none
[262]/0/263/34139/0x4d7c1620/15053/IGN/425/426//none
[263]/0/264/22439/0x4d7c20b0/14211/IGN/427/428//none
[264]/0/265/19012/0x4d7c2b40/14436/IGN/429/430//none
[266]/0/267/42505/0x4d7c4060/14653/IGN/431/432//none
[267]/0/268/3707/0x4d7c4af0/15103/IGN/433/434//none
[270]/0/271/30446/0x4d7c6aa0/15111/IGN/435/436//none
[273]/0/274/3461/0x4d7c8a50/28164/IGN/437/438//none
[274]/0/275/802/0x4d7c94e0/14934/IGN/439/440//none
[277]/0/278/34440/0x4d7cb490/28162/IGN/441/442//none
[280]/0/281/62790/0x4d7cd440/28156/IGN/443/444//none
[294]/0/295/57125/0x4d7d6820/19237/IN_HANG/396/401/[294][238][253]/238
[311]/0/312/10459/0x4d7e1bb0/25247/SINGLE_NODE_NW/445/446//none
[324]/0/325/61185/0x4d7ea500/8815/IGN/447/448//none
[328]/0/329/43816/0x4d7ecf40/23487/IN_HANG/398/399/[253]/253
[344]/0/345/65234/0x4d7f7840/20883/IGN/449/450//none
[352]/0/353/50790/0x4d7fccc0/28187/IGN/451/452//none
[360]/0/361/17554/0x4d802140/28166/IGN/453/454//none
[363]/0/364/10549/0x4d8040f0/28193/IGN/455/456//none
Dumping System_State and Fixed_SGA in process with ospid 13476
Dumping Process information for process with ospid 13476
Dumping Process information for process with ospid 23307
Dumping Process information for process with ospid 19237
Dumping Process information for process with ospid 23487
====================
END OF HANG ANALYSIS
====================
*** 2009-05-20 16:48:20.686

我们来看看([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor)部分：
nodenum是hanganalyze自己为了记录这些会话而定制的编号，从0开始排起。
cnode是Node Id
sid是 Session ID
sess_srno是serial#
proc_ptr是Process Pointer
ospid 是OS Process ID
state 是node的状态
adjlist是临近的node(通常代表一个blocker node)
predecessor是Predecessor node ,通常代表一个 waiter node

state部分有几个比较重要的状态：
1.IN_HANG：这表示该node处于死锁状态，通常还有其他node（blocker）也处于该状态
2.LEAF/LEAF_NW：该node通常是blocker。通过条目的”predecessor”列可以判断这个node是否是blocker。LEAF说明该NODE没有等待其他资源，而LEAF_NW则可能是没有等待其他资源或者是在使用CPU.
3.NLEAF：通常可以看作这些会话是被阻塞的资源。发生这种情况一般说明数据库发生性能问题而不是数据库hang
4.IGN/IGN_DMP：这类会话通常被认为是空闲会话，除非其adjlist列里存在node。如果是非空闲会话则说明其adjlist里的node正在等待其他node释放资源。
5.SINGLE_NODE/SINGLE_NODE_NW：近似于空闲会话

现在我们来看看我们的trace出来的文件：
Cycle 1 : :
<0/329/43816/0x4d6b5638/23487/row cache lock>
— <0/254/19761/0x4d687438/23307/library cache lock>
Cycle 2 : :
<0/295/57125/0x4d6b8978/19237/row cache lock>
Cycle 3 : :
<0/295/57125/0x4d6b8978/19237/row cache lock>

cycle表示oracle内部确定的死锁。其中我们的当前手工执行split的295进程也在里面。
我们观察其他的进程在做什么，如329：

SQL> l
  1  select machine,status,program,sql_text from v$session a ,v$sqlarea b
  2* where a.sql_address=b.address and a.sid=329
SQL > /
 
MACHINE                                                          STATUS
-------------------------------------------------------------- -- --------
PROGRAM
---------------------------------------------- --
SQL_TEXT
------------------------------------------------------------------------------ --
hl_rdb01                                                         ACTIVE
sqlplus@hl_rdb01 (TNS V1-V3)
ALTER TABLE A_PDA_SP_STAT SPLIT PARTITION P_MAX AT (20090609)  INTO (PARTITION P
_20090608 TABLESPACE TS_DATA_A  , PARTITION P_MAX TABLESPACE TS_DATA_A)
 
 
SQL> select event from v$session_wait where sid=329 ;
 
EVENT
-------------------------------------------------------------- --
row cache lock

发现也是在执行split语句，但是问了同事，他已经把之前运行失败的脚本完全kill掉了。
估计在数据库中进程挂死了，没有完全的释放。
kill掉329号进程后，发现还是挂住，所以我们继续做hanganlyze：

==============
HANG ANALYSIS:
==============
 
Cycle 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/295/57125/0x4d6b8978/19237/row cache lock>
Cycle 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> :
    <0/254/19761/0x4d687438/23307/library cache lock>
 -- <0/239/57618/0x4d6b74f8/13476/row cache lock>

我们继续把其他的进程杀掉。终于295的split执行成功。

SQL> l
  1  ALTER TABLE A_PDA_SP_STAT SPLIT PARTITION P_MAX AT (20090609)
  2   INTO (PARTITION P_20090608 TABLESPACE TS_DATA_A
  3*  , PARTITION P_MAX TABLESPACE TS_DATA_A)
SQL > /
                                                
      
Table altered .
 
Elapsed: 00:31:03.21
SQL> SQL >
SQL>

继续执行split下一个分区，也很快完成。

SQL >
SQL >
SQL >
SQL> ALTER TABLE A_PDA_SP_STAT SPLIT PARTITION P_MAX AT (20090610)
  2   INTO (PARTITION P_20090609 TABLESPACE TS_DATA_A
  3   , PARTITION P_MAX TABLESPACE TS_DATA_A) ;
 
Table altered .
 
Elapsed: 00:00:00.02
SQL>

至此，问题解决。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-681031/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-681031/