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

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

The following experiment simulates the reasons why the 2 temporary tables were used improperly to report ora-1445o:

1. Temporary tables based on things

sql> Create Global Temporary tabletemp_tab on commit Delete rowsasselect ' A ' as A1 fromdual;

Table created.

sql> Select Sid from V$mystat whererownum=1;

SID

----------

191

sql> Insert into Temp_tabvalues(' B ');

1 row created.

Sql>

Do not commit in 191, open another session Execute DDL (if DDL statements can be executed successfully in the same session)

sql> Alter tabletemp_tabadd b1varchar2(ten);

Alter tabletemp_tabadd b1varchar2(ten)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional Temp table already in use

sql> Select Sid from V$mystat whererownum=1;

SID

----------

+

Sql>

Return to session 191 Execute custom Things

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 temp Table already in use

ORA-06512: at line4

2. Temporary table based on session

sql> Drop tabletemp_tabpurge;

Table dropped.

sql> Create Global Temporary tabletemp_tab on commit Preserve rowsasselect ' A ' as A1 fromdual;

Table created.

sql> Select Sid from V$mystat whererownum=1;

SID

----------

191

sql> Insert into Temp_tabvalues(' a ');

1 row created.

sql> Alter tabletemp_tabadd b1varchar2(ten);

Alter tabletemp_tabadd b1varchar2(ten)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temp Table already in use

sql> Select *from Temp_tab;

A

-

A

A

sql> commit;

Commit Complete .

sql> Alter tabletemp_tabadd b1varchar2(ten);

Alter tabletemp_tabadd b1varchar2(ten)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temp Table already in use

The above experiment can see that the temporal table based on the session in the same session to run a thing, whether it is committed or uncommitted can not do DDL operations, and then run a thing, do not commit to another session to do DDL error is also wrong

sql> Delete from Temp_tab;

2 rows deleted.

sql> Select Sid from V$mystat whererownum=1;

SID

----------

+

sql> Alter tabletemp_tabadd b1varchar2(ten);

Alter tabletemp_tabadd b1varchar2(ten)

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable already in use

After the submission, in another session test found still cannot DDL

sql> commit;

Commit Complete .

Exit session, only one session to do DDL

sql> Alter tabletemp_tabadd b1varchar2(ten);

Table altered.

3. Summary:

A , transaction-based temporary table in the same session is not allowed to do custom things, and if in a session run a thing did not commit, in another session can not do DDL operation

B, session-based temporal table if you run a thing in a Sesson, any session doesn't run. DDL includes the session that runs the thing

4. Resolve ORA-14450 Errors

-- Find Sid and serial#

sql> SELECT DISTINCT A.sid,a. serial#from V$sessionA,v$sql b,V$ENQUEUE_LOCKC

2 WHERE a.sid=c. SID and C. Type=' to 'andA. Module_hash=b. Module_hashandLower (b. Sql_text)like'%tmp_tab% ';

SID serial#

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

191

166

-- Use SYS User Login

sql> Alter system Kill Session ' 191,264 ' ;

System Altered

sql> Alter system Kill Session ' 40,166 ' ;

sql>

-- Back to Scott

sql> ALTER TABLE temp_tabADD c number(2) ;

Table Altered