A ORA-14450 attempt to access a transactional temp table already in use, ora14450

A ORA-14450 attempt to access a transactional temp table already in use, ora14450

An error occurred today when adding a field to the temporary table: ORA-14450 attempt to access a transactional temp table already inuse

The reasons for improper use of two temporary tables for ORA-1445O are simulated based on the experiment below:

1. Temporary transaction-based tables

SQL> createglobaltemporary tabletemp_tabon commit delete rowsasselect 'A' as a1 fromdual;

Table created.

SQL> selectsidfromv $ mystat whererownum = 1;

SID

----------

191

SQL> insertintotemp_tabvalues ('B ');

1 row created.

SQL>

When the 191 session is not committed, open another session to execute the ddl statement. (if the session is in the same session, the ddl statement can be successfully executed)

SQL> altertabletemp_tabadd b1varchar2 (10 );

Alter tabletemp_tabadd b1varchar2 (10)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable alreadyinuse

SQL> selectsidfromv $ mystat whererownum = 1;

SID

----------

40

SQL>

Return session 191 to execute the custom transaction

SQL> declare

2 pragmaautonomous_transaction;

3 begin

4 insert into temp_tab values ('C ');

5 commit;

6 end;

7/

Declare

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable alreadyinuse

ORA-06512: at line4

2. session-based temporary table

SQL> droptabletemp_tabpurge;

Table dropped.

SQL> createglobaltemporary tabletemp_tabon commit preserve rowsasselect 'A' as a1 fromdual;

Table created.

SQL> selectsidfromv $ mystat whererownum = 1;

SID

----------

191

SQL> insertintotemp_tabvalues ('A ');

1 row created.

SQL> altertabletemp_tabadd b1varchar2 (10 );

Alter tabletemp_tabadd b1varchar2 (10)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable alreadyinuse

SQL> select * fromtemp_tab;

A

-

A

A

SQL> commit;

Commit complete.

SQL> altertabletemp_tabadd b1varchar2 (10 );

Alter tabletemp_tabadd b1varchar2 (10)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable alreadyinuse

The above experiment shows that a session-based temporary table runs a transaction in the same session. No ddl operation can be performed for both commit and uncommit tasks, and another transaction can be run, the same error is reported if you do not submit data to another session for ddl operation.

SQL> deletefrom temp_tab;

2 rows deleted.

SQL> selectsidfromv $ mystat whererownum = 1;

SID

----------

40

SQL> altertabletemp_tabadd b1varchar2 (10 );

Alter tabletemp_tabadd b1varchar2 (10)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable alreadyinuse

After the submission, the ddl statement is still unavailable during another session test.

SQL> commit;

Commit complete.

Exit the session. Only one session is used for ddl.

SQL> altertabletemp_tabadd b1varchar2 (10 );

Table altered.

3. Summary:

A. A transaction-based temporary table does not allow custom transactions in the same session. If A transaction is run in one session and not committed, ddl operations cannot be performed in another session.

B. If a session-based temporary table runs a transaction in a certain sesson, no session will run ddl, including the session that runs the transaction.

 

4. Solve ORA-14450 errors

-- Find sid and serial #

SQL> SELECTDISTINCTa. sid, a. SERIAL # FROMv $ sessiona, v $ SQL B, v $ enqueue_lockc

2 WHERE a. SID = c. SIDAND c. TYPE = 'to 'anda. MODULE_HASH = B. MODULE_HASHANDlower (B. SQL _TEXT) LIKE '% tmp_tab % ';

Sid serial #

--------------------

191 264

40 166

-- Log On with the sys user

SQL> altersystemkill session '123 ';

System altered

SQL> altersystemkill session '123 ';

SQL>

-- Return to scott

SQL> ALTERTABLE temp_tabADD cNUMBER (2 );

Table altered