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

最新推荐文章于 2021-04-16 04:52:53 发布

congyun1447

最新推荐文章于 2021-04-16 04:52:53 发布

阅读量120

点赞数

最近数据库中莫名在执行"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)