Bug Attributes

Related Products

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)