http://space.itpub.net/195110/viewspace-734004
最近 数据库中莫名在 执行"delete from A GLOBAL TEMPORARY TABLE "时报"ORA-14450: attempt to access a transactional temp table already in use"错误,但重新执行相关的业务又正常,但过一段时间又出现.
最近 数据库中莫名在 执行"delete from A GLOBAL TEMPORARY TABLE "时报"ORA-14450: attempt to access a transactional temp table already in use"错误,但重新执行相关的业务又正常,但过一段时间又出现.
环境:
Oracle Database
10g Enterprise Edition Release 10.2.0.4.0 - 64bi
ITPUB个人空间6L%WB;^xY
PL/SQL Release 10.2.0.4.0 - Production
9hwgV!D%Hnx0CORE 10.2.0.4.0 Production
Mc-_3}R(\]/@0TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
:h@ ?7c$g&t%^0NLSRTL Version 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
9hwgV!D%Hnx0CORE 10.2.0.4.0 Production
Mc-_3}R(\]/@0TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
:h@ ?7c$g&t%^0NLSRTL Version 10.2.0.4.0 - Production
临时表类型为:commit delete rows;
查资料发现现象与Bug 5334271的描述非常吻合,但Oracle目前没有相关的补丁.
后把临时表的类型改为commit preserve rows,错误竟然再也没有出现.
附Bug 5334271的内容:
Bug 5334271: SPORADIC ORA-14450 ERROR DELETING FROM A GLOBAL TEMPORARY TABLE | |||||
|
Bug Attributes
w#x&Eyro2K0
Type | B - Defect | Fixed in Product Version | - |
Severity | 2 - Severe Loss of Service | Product Version | 10.1.0.4.0 |
Status | 92 - Closed, Not a Bug | Platform | 215 - z*OBSOLETE: Microsoft Windows Server 2003 |
Created | 14-Jun-2006 | Platform. Version | 5.2 |
Updated | 28-Jun-2006 | Base Bug | - |
Database Version | 10.1.0.4.0 | ||
Affects Platforms | Generic | ||
Product Source | Oracle |
Related Products
zLC)rMQ6e1L(C0
Line | Oracle Database Products | Family | Oracle Database |
Area | Oracle Database | Product | 5 - Oracle Server - Enterprise Edition |
Hdr: 5334271 10.1.0.4.0 RDBMS 10.1.0.4.0 PRODID-5 PORTID-215 ORA-14450 ?~(tv+XSO*id$X0Abstract: SPORADIC ORA-14450 ERROR DELETING FROM A GLOBAL TEMPORARY TABLE ^(\*g3?C;b|0ITPUB个人空间Cnw)?8Rb_I-k *** 06/14/06 02:10 pm *** K[ @i/lU i)b-b0s5t }0TAR:ITPUB个人空间 _ gl8l8{(|!{8Ib ---- pk^ D i7_}t0 +qdo8]m)I7D0PROBLEM: %Vh/JN3G2h0--------ITPUB个人空间 Tw w^Ea.Pmn Application runs fine greater than 90% of time, then out of the blue will y`U:zb0fail with an ORA-14450 error. 3E']7g!@Gu$pe0ITPUB个人空间 Zvc[V*l'K%M From an event trace.
*** 15:49:47.796 dQ5e(E9S8IR0ksedmp: internal or fatal errorITPUB个人空间^m]X#X9q9k ORA-14450: attempt to access a transactional temp table already in useITPUB个人空间 mY1?gqVR6b Current SQL statement for this session: 2x:|#OsYJm7l)M@0D0DELETE FROM SMPLCANDIDATESITPUB个人空间4LPR*AMF%x ----- PL/SQL Call Stack ----- \R[XiRY9cY0object line objectITPUB个人空间hzY%z4dS8\F"O handle number name H J%FL9I(Y^8qi|7s029E8D0CC 83 procedure QC_CDB.SPIS_MPMLSTEP1ITPUB个人空间/Q&?#Vf%vqL 29E93700 1 anonymous block E3|7I N7o1_.f4ke0 8x-vy/Gco0DIAGNOSTIC ANALYSIS: (DFYqP~0-------------------- +eoJ8|R AuO1x0Have tried to reproduce using application code, but no luck getting the _4qfF6`cS_]0failure to occur.ITPUB个人空间4_*d#j3P U9J$m io-W(z !w5z9]o5w{aw0SQL> CREATE GLOBAL TEMPORARY TABLE SCOTT.SMPLCANDIDATESITPUB个人空间(hv3d*@K-C }h_ 2 (ITPUB个人空间ayU7a0S+lW 3 MPID NUMBER(38) NOT NULL,ITPUB个人空间ts;p)cz Ote\8i 4 CLUSTERID NUMBER(38) NOT NULL, 3v nZcv%e{E-?05 ALINKCOUNT NUMBER(38) NULL, Q,i*eT+SQYn k06 CONSTRAINT PKSMPLCANDIDATES PRIMARY KEY (MPID, CLUSTERID)ITPUB个人空间!^5qsG nCF 7 )ITPUB个人空间'K&v@|'\&[ 8 ON COMMIT DELETE ROWSITPUB个人空间 w0f}LG 9 / 9hCDl$B3JhTd^7j0 7w ~Iy1b't"B!I"J*{|0Table created. bG3rjcxu0ITPUB个人空间cj#@*X4c9i"L3y SQL> DELETE FROM smplcandidates; @K"E6G9l0 :|8M:Ye&~00 rows deleted.ITPUB个人空间CG|$x3{T#n\ lqF ZB'jM-bG/R-|5UC0SQL> INSERT INTO smplcandidatesITPUB个人空间^i[+q u'g[M*D 2 select empno, mgr, deptno from emp;ITPUB个人空间;j(`@p.T V&Fo3ODZ6N9b014 rows created. H ZE$WW"JH1k0 ]k3xfiv'e0SQL> select count(*) from smplcandidates; 6mGk4H0NZa3q0 ^#Uc]TM.}o!L0COUNT(*) g"C&L[ P{%n0----------ITPUB个人空间 ]xFI0JA 14 *pW0oOO5dy/Q0ITPUB个人空间_ c;h"YJx SQL> commit;ITPUB个人空间 \wyD"G$a `1qT-|M$w X)fu0Commit complete.ITPUB个人空间3DYv/cm(t?L~ ITPUB个人空间a%rkcL%LTD SQL> select count(*) from smplcandidates; +AB9n9O`M9dd0ITPUB个人空间6p9w$fy-B'H%D1R@ COUNT(*) TuD F8G}0---------- c#D1~_Q7V!q00 5UPI\xu0 ?A }.ZK8|C(]M0SQL> INSERT INTO smplcandidatesITPUB个人空间\j8Pir RgO#O 2 select empno, mgr, deptno from emp;ITPUB个人空间:ug/`YEn 7Eo(haQMT%sX014 rows created.ITPUB个人空间;~?&LjsC1J ITPUB个人空间n D+OC"Z+~6H SQL> DELETE FROM smplcandidates;ITPUB个人空间.l!^-z)~6Vt(g ITPUB个人空间-j'IG2U;}:M 14 rows deleted. {Sj!u1UT0ITPUB个人空间'tl L|,N SQL> select count(*) from smplcandidates; *Ri(tL+[Xc)p@0 +?/c rnl%r0COUNT(*) ati,r#L*VK3[_leI({0---------- Q[\@ ir"\3\a00ITPUB个人空间 ]+sX-N R T |Hb(Wm ITPUB个人空间D2Ob!?4U8H Have tried 2 and 3 concurrent sessions inserting and deleting, but cannot getITPUB个人空间 @3Nd}*o#r this to fail. Only fails at ct. site using application, and then only very M$s?n%K,H_|W0sporadically. dy"Z(ypOO rh+D0ITPUB个人空间_ K$b9ct T L0y4X WORKAROUND:ITPUB个人空间n*P;`/f.e+K)L ----------- x+bpBG3pY#dn K#O0Re-run the process that just failed, it will run fine. &o\fg;m v1\0 ,S ]F\M,V(hfY0RELATED BUGS:ITPUB个人空间fUne\t#r\ -------------ITPUB个人空间~ B$v8p? j)\dB!d,a ZS5g| U'm!ra#c!J0REPRODUCIBILITY:ITPUB个人空间yZ M cBw)h| ----------------ITPUB个人空间G3FOee%] Unable to reproduce, only at ct. site. 0O*b8p\d!i0 s;H'I4_n;MS0TEST CASE: u/A_ ~;xHK*d0---------- %\,h OHm(g;C0 'xTAW_Vb$g0STACK TRACE:ITPUB个人空间 Fi}CwCp\3I ------------ITPUB个人空间X"PKe*i8@M+T)K *** 15:49:47.796ITPUB个人空间 s AB g]{} ksedmp: internal or fatal error w!V%?h*PKs$g0ORA-14450: attempt to access a transactional temp table already in use .a_f;}4Db0Current SQL statement for this session: ])Z)w^pj\q.}N0DELETE FROM SMPLCANDIDATESITPUB个人空间ul8q(YN!e ----- PL/SQL Call Stack ----- 9jf9I@O0object line objectITPUB个人空间r:E#wI!ER8\&bmOg handle number name 1E:[oY*\TZ4@2L$A029E8D0CC 83 procedure QC_CDB.SPIS_MPMLSTEP1 Ss5|kzUZ029E93700 1 anonymous blockITPUB个人空间VH l4c"{,l~ ----- Call Stack Trace ----- P?bkeA7Xk0calling call entry argument values in hex M[m)ib0 -q PP |CG0location type point (? means dubious value) M3b:o;?&b,_F M0 JbA)c8j _&ydy f0-------------------- -------- --------------------ITPUB个人空间,R2XwQ7LSk.e ----------------------------ITPUB个人空间z {6ld-?| _ksedmp+576 CALLrel _ksedst+0 0 VJ'[U,^'EP#?.c1{0_ksddoa+122 CALLreg 00000000 3ITPUB个人空间*f$t}"y8E0M]V _ksdpcg+143 CALLrel _ksddoa+0ITPUB个人空间rc5fj9@pU/E _ksdpec+180 CALLrel _ksdpcg+0 3872 E5DC0C8 1 xt'L7m,E"O0__PGOSF3__ksfpec+11 CALLrel _ksdpec+0 0ITPUB个人空间(^&e[3vS 8 ^ s3?;xv#c1lF0_kgesev+81 CALLreg 00000000 BF31BB0 3872 'Y*[KW%F.G)g!C0_ksesec0+39 CALLrel _kgesev+0 BF31BB0 E2FE63C 3872 0 ]!l-k&VcE rt3ls'h,@0E5DC11C H;?9J:?(c8E|H8Y0_kctphTTGet+38 CALLrel _ksesec0+0 3872 B"LqtC5{0__VInfreq__delini+3 CALLrel _kctphTTGet+0 234F8F24 5F82C68 %cei_ Q+Il028ITPUB个人空间6a p,_%Aku2c _delexe+149 CALLrel _delini+0 26A2F314 5F8293CITPUB个人空间}7BJ!aG/S(Y/Um _opiexe+13427ITPUB个人空间^j"gu1vhU o }u/TAapc0SUPPORTING INFORMATION:ITPUB个人空间&UAu;nCT'A3W*k -----------------------ITPUB个人空间vob!D!FZ,gW ITPUB个人空间'^OU,Ihz }n 24 HOUR CONTACT INFORMATION FOR P1 BUGS:ITPUB个人空间g2x/IAS(@ ---------------------------------------- h/s1})XZ Av6o0 ^!P(O0` m.p0DIAL-IN INFORMATION:ITPUB个人空间%o1DgB,@/Q -------------------- @vp(DJJ M0ITPUB个人空间3u3q*\%` t%_^u*DP IMPACT DATE:ITPUB个人空间:bn&}Q9N8^~!R ------------ /Z5}.VQ D'W$a[1my0 )|:|_[|hX\+z0*** 06/14/06 02:18 pm *** *ci.xdJ4kd\V0Uploaded the following to "/upload/bug5334271" g"w |0cA.@0 *kWU)b9c9Z6e0RDA.HSMPQC_OJSISQL103.zip - RDA report e A+C$jxj/f3J|+R0trace_files.zip - event 14450 tracesITPUB个人空间7L2WD"\J#T ITPUB个人空间jz:fDT7A The event traces where generated with the following and unable to find other 1T/t#_Kn,NW)x0users even touching the failing temp table.ITPUB个人空间0^ @oF;W{C7D0]6MK ITPUB个人空间5W.E\/hJ+sN#D event="14450 trace name errorstack level 3"ITPUB个人空间e%d1pU9mZ7@$H7W event="14450 trace name systemstate level 10" ^Nf9aq LQ0ITPUB个人空间2T8O@P+uQ2H#[ Please let me know what additional information is needed.ITPUB个人空间gM&y {K4Y,o$p *** 06/21/06 12:48 pm ***ITPUB个人空间3pQz;t!A{G*G *** 06/25/06 09:40 pm *** ESCALATEDITPUB个人空间NB8jd(L)\ C *** 06/25/06 09:40 pm *** WI-f p!yE&?S-Q0*** 06/25/06 10:30 pm *** q#] fKKu0*** 06/26/06 09:16 pm ***ITPUB个人空间H m_0Gq"UXZ *** 06/27/06 11:04 am ***ITPUB个人空间B7{I`m3RS"_ *** 06/27/06 11:05 am *** (CHG: Sta->10) .`b:}RF:I!NM6j*je0*** 06/27/06 11:05 am *** ?9Z9zR/n6bk'J0*** 06/28/06 07:12 am *** \k&T6e&s0*** 06/28/06 07:12 am *** -> CLOSEDITPUB个人空间0tkn#N Y(d7ZD_e *** 06/28/06 07:12 am *** (CHG: Sta->92) ;R/mXR0D2GE{-|/]0