ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 记录: ORA-14450 ERROR DELETING FROM A GLOBAL TEMPORARY TABLE Bug 5334271

记录: ORA-14450 ERROR DELETING FROM A GLOBAL TEMPORARY TABLE Bug 5334271

原创 Linux操作系统 作者:denglt 时间:2012-06-28 09:12:20 0 删除 编辑
最近数据库中莫名在 执行"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
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL 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

Show Bug Attributes Bug Attributes


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

Show Related Products Related Products


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
Abstract: SPORADIC ORA-14450 ERROR DELETING FROM A GLOBAL TEMPORARY TABLE

*** 06/14/06 02:10 pm ***
TAR:
----

PROBLEM:
--------
Application runs fine greater than 90% of time, then out of the blue will
fail with an ORA-14450 error.


From an event trace.
*** 15:49:47.796
ksedmp: internal or fatal error
ORA-14450: attempt to access a transactional temp table already in use
Current SQL statement for this session:
DELETE FROM SMPLCANDIDATES
----- PL/SQL Call Stack -----
object line object
handle number name
29E8D0CC 83 procedure QC_CDB.SPIS_MPMLSTEP1
29E93700 1 anonymous block

DIAGNOSTIC ANALYSIS:
--------------------
Have tried to reproduce using application code, but no luck getting the
failure to occur.


SQL> CREATE GLOBAL TEMPORARY TABLE SCOTT.SMPLCANDIDATES
2 (
3 MPID NUMBER(38) NOT NULL,
4 CLUSTERID NUMBER(38) NOT NULL,
5 ALINKCOUNT NUMBER(38) NULL,
6 CONSTRAINT PKSMPLCANDIDATES PRIMARY KEY (MPID, CLUSTERID)
7 )
8 ON COMMIT DELETE ROWS
9 /

Table created.

SQL> DELETE FROM smplcandidates;

0 rows deleted.

SQL> INSERT INTO smplcandidates
2 select empno, mgr, deptno from emp;

14 rows created.

SQL> select count(*) from smplcandidates;

COUNT(*)
----------
14

SQL> commit;

Commit complete.

SQL> select count(*) from smplcandidates;

COUNT(*)
----------
0

SQL> INSERT INTO smplcandidates
2 select empno, mgr, deptno from emp;

14 rows created.

SQL> DELETE FROM smplcandidates;

14 rows deleted.

SQL> select count(*) from smplcandidates;

COUNT(*)
----------
0

Have tried 2 and 3 concurrent sessions inserting and deleting, but cannot get
this to fail. Only fails at ct. site using application, and then only very
sporadically.

WORKAROUND:
-----------
Re-run the process that just failed, it will run fine.

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
Unable to reproduce, only at ct. site.

TEST CASE:
----------

STACK TRACE:
------------
*** 15:49:47.796
ksedmp: internal or fatal error
ORA-14450: attempt to access a transactional temp table already in use
Current SQL statement for this session:
DELETE FROM SMPLCANDIDATES
----- PL/SQL Call Stack -----
object line object
handle number name
29E8D0CC 83 procedure QC_CDB.SPIS_MPMLSTEP1
29E93700 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex

location type point (? means dubious value)

-------------------- -------- --------------------
----------------------------
_ksedmp+576 CALLrel _ksedst+0 0
_ksddoa+122 CALLreg 00000000 3
_ksdpcg+143 CALLrel _ksddoa+0
_ksdpec+180 CALLrel _ksdpcg+0 3872 E5DC0C8 1
__PGOSF3__ksfpec+11 CALLrel _ksdpec+0 0
8
_kgesev+81 CALLreg 00000000 BF31BB0 3872
_ksesec0+39 CALLrel _kgesev+0 BF31BB0 E2FE63C 3872 0
E5DC11C
_kctphTTGet+38 CALLrel _ksesec0+0 3872
__VInfreq__delini+3 CALLrel _kctphTTGet+0 234F8F24 5F82C68
28
_delexe+149 CALLrel _delini+0 26A2F314 5F8293C
_opiexe+13427

SUPPORTING INFORMATION:
-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 06/14/06 02:18 pm ***
Uploaded the following to "/upload/bug5334271"

RDA.HSMPQC_OJSISQL103.zip - RDA report
trace_files.zip - event 14450 traces

The event traces where generated with the following and unable to find other
users even touching the failing temp table.

event="14450 trace name errorstack level 3"
event="14450 trace name systemstate level 10"

Please let me know what additional information is needed.
*** 06/21/06 12:48 pm ***
*** 06/25/06 09:40 pm *** ESCALATED
*** 06/25/06 09:40 pm ***
*** 06/25/06 10:30 pm ***
*** 06/26/06 09:16 pm ***
*** 06/27/06 11:04 am ***
*** 06/27/06 11:05 am *** (CHG: Sta->10)
*** 06/27/06 11:05 am ***
*** 06/28/06 07:12 am ***
*** 06/28/06 07:12 am *** -> CLOSED
*** 06/28/06 07:12 am *** (CHG: Sta->92)
<!--5334271
 

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

请登录后发表评论 登录
全部评论

注册时间:2010-11-04

  • 博文量
    118
  • 访问量
    688982