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

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

                今天在對暫存資料表新增欄位時遇到了錯誤：ORA-14450 attempt to access a transactional temp table already inuse

下面根據實驗類比了2種暫存資料表使用不當報ORA-1445O的原因：

1、基於事物的暫存資料表

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>

在191回話不做commit，開啟另外一個session 執行ddl（如果在同一會話可以成功執行ddl語句）

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>

返回session 191 執行自訂事物

SQL> declare

 2  pragmaautonomous_transaction;

 3  begin

 4    insert intotemp_tab values ('c');

 5    commit;

 6  end;

 7  /

declare

*

ERROR at line1:

ORA-14450: attempttoaccessa transactional temptable alreadyinuse

ORA-06512:at line4

2、基於session 的暫存資料表

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

上面實驗可以看出基於session 的暫存資料表在同一個session內運行一個事物，無論是提交還是未提交都不能做ddl操作，再運行一個事物，不提交到另外一個session做ddl報錯同樣錯誤

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

提交之後，在另外session測試發現還是無法ddl

SQL> commit;

Commit complete.

退出session，僅有一個會話做ddl

SQL> altertabletemp_tabadd b1varchar2(10);

Table altered.

3、總結：

A、基於transaction的暫存資料表在同一個session裡面不允許做自訂事物，並且若在一個session裡面運行了一條事物未做提交，在另外一個session無法做ddl操作

B、基於session的暫存資料表若在某一個sesson裡面運行了事物，任何session都不運行做ddl包括運行該事物的session

 

4、解決ORA-14450錯誤

--尋找sid及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

--使用sys使用者登陸

SQL> altersystemkill session '191,264';

System altered

SQL> altersystemkill session '40,166';

SQL>

--回到scott

SQL> ALTERTABLE temp_tabADD cNUMBER(2) ;

Table altered