ORA-14450: attempt to access a transactional temp table already in use, temptable

ORA-14450: attempt to access a transactional temp table already in use, temptable

ORA-14550 errors may occur when modifying session-level temporary tables in ORACLE data, so why does the session-level global temporary table report a ORA-14450 error, as shown below, let's start with a small case:

Case 1:

SQL> CREATE GLOBAL TEMPORARY TABLE TEMP_TEST

  2  (

  3     NAME VARCHAR2(12)

  4  ) ON COMMIT PRESERVE ROWS;

Table created.

SQL> INSERT INTO TEMP_TEST VALUES('kerry');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE TEMP_TEST ADD SEX NUMBER(1) ;

ALTER TABLE TEMP_TEST ADD SEX NUMBER(1)

*

ERROR at line 1:

ORA-14450: attempt to access a transactional temp table already in use

As shown above, there was a ORA-14450 error when modifying the session-level temporary table, so what are the solutions? In this case, you need to disconnect the session or execute the TRUNCATE statement:

SQL> TRUNCATE TABLE TEMP_TEST;

Table truncated.

SQL>  ALTER TABLE TEMP_TEST ADD SEX NUMBER(1) ;

Table altered.

SQL>

As shown in the following figure, we simulate a session to operate the temporary table TEMP_TEST. The other session is to be modified as follows (the actual scenario may be more complex and may involve multiple sessions instead of just two sessions)

Session 1:

SQL> SET SQLPROMPT "SQLSESSION 1 >"

SQLSESSION 1 >CREATE GLOBAL TEMPORARY TABLE TEMP_TEST

  2           (

  3              NAME VARCHAR2(12)

  4           ) ON COMMIT PRESERVE ROWS;

Table created.

SQLSESSION 1 >INSERT INTO TEMP_TEST VALUES('kerry');

1 row created.

SQLSESSION 1 >COMMIT;

Commit complete.

SQLSESSION 1 >

Session 2 ::

SQL> SET SQLPROMPT "SESSION 2 >"

SESSION 2 >ALTER TABLE TEMP_TEST ADD SEX NUMBER(1) ;

ALTER TABLE TEMP_TEST ADD SEX NUMBER(1)

*

ERROR at line 1:

ORA-14450: attempt to access a transactional temp table already in use

SESSION 2 >

In this case, Session 1 is logged on by another user. For example, you cannot require all sessions to perform the TRUNCATE operation. What should you do at this time?

In this case, you can use the following steps to solve the problem.

Step 1. log on to the database using sys or system and query the OBJECT_ID of the table from DBA_OBJECTS:

SELECT OWNER, OBJECT_ID,OBJECT_TYPE 

  FROM DBA_OBJECTS 

    WHERE OBJECT_NAME=&OBJECT_NAME AND OBJECT_TYPE ='TABLE';

Step 2. Know the SESSION of the table based on the retrieved OBJECT_ID:

SELECT ADDR, KADDR, SID,LMODE FROM V$LOCK WHERE ID1=&OBJECT_ID;

Step 3. Use the following SQL statement to find the corresponding SESSION and generate the execution statement of the KILL SESSION

SET COL kill_session FOR A80;

select a.sid, a.serial#,a.status,

       a.paddr, 'alter system kill session ''' 

                    || a.sid || ',' || a.serial# 

                    || ''' immediate;' AS kill_session

FROM v$session a

WHERE a.sid in (select sid from v$enqueue_lock t where t.type='TO') 

  and a.sid=&sid;

Step 4. view the SESSION Status and execute the alter system kill session Statement to KILL these processes:

The procedure is as follows:

Cause: Check the ORA-14450 error, you can see the following information:

[Oracle @ oracle-server ~] $ Oerr ora 1, 14450
14450,000 00, "attempt to access a transactional temp table already in use"
// * Cause: An attempt was made to access a transactional temporary table that
// Has been already populated by a concurrent transaction of the same
// Session.
// * Action: do not attempt to access the temporary table until
// Concurrent transaction has committed or aborted.