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

                今天在对临时表新增栏位时遇到了错误：ORA-14450 attempt to access a transactional temp table already inuse

下面根据实验模拟了2种临时表使用不当报ORA-1445O的原因：

1、基于事物的临时表

SQL> createglobal temporary tabletemp_tab on commit delete rowsas select ‘a‘ as a1 fromdual;

Table created.

SQL> selectsid fromv$mystat where rownum=1;

      SID

----------

      191

SQL> insertintotemp_tab values(‘b‘);

1 row created.

SQL>

在191回话不做commit，打开另外一个session 执行ddl（如果在同一会话可以成功执行ddl语句）

SQL> altertabletemp_tab add b1varchar2(10);

alter tabletemp_tabadd b1 varchar2(10)

*

ERROR at line1:

ORA-14450: attempttoaccess a transactional temp table alreadyinuse

SQL> selectsid fromv$mystat where rownum=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: attempttoaccess a transactional temp table alreadyinuse

ORA-06512:at line 4

2、基于session 的临时表

SQL> droptabletemp_tab purge;

Table dropped.

SQL> createglobal temporary tabletemp_tab on commit preserve rowsas select ‘a‘ as a1 fromdual;

Table created.

SQL> selectsid fromv$mystat where rownum=1;

      SID

----------

      191

SQL> insertintotemp_tab values(‘a‘);

1 row created.

SQL> altertabletemp_tab add b1varchar2(10);   

alter tabletemp_tabadd b1 varchar2(10)

*

ERROR at line1:

ORA-14450: attempttoaccess a transactional temp table alreadyinuse

SQL> select *fromtemp_tab;

A

-

a

a

SQL> commit;

Commit complete.

SQL> altertabletemp_tab add b1varchar2(10);

alter tabletemp_tabadd b1 varchar2(10)

*

ERROR at line1:

ORA-14450: attempttoaccess a transactional temp table alreadyinuse

上面实验可以看出基于session 的临时表在同一个session内运行一个事物，无论是提交还是未提交都不能做ddl操作，再运行一个事物，不提交到另外一个session做ddl报错同样错误

SQL> deletefrom  temp_tab;

2 rows deleted.

SQL> selectsid fromv$mystat where rownum=1;

      SID

----------

      40

SQL> altertabletemp_tab add b1varchar2(10);

alter tabletemp_tabadd b1 varchar2(10)

*

ERROR at line1:

ORA-14450: attempttoaccess a transactional temp table alreadyinuse

提交之后，在另外session测试发现还是无法ddl

SQL> commit;

Commit complete.

退出session，仅有一个会话做ddl

SQL> altertabletemp_tab add b1varchar2(10);

Table altered.

3、总结：

A、基于transaction的临时表在同一个session里面不允许做自定义事物，并且若在一个session里面运行了一条事物未做提交，在另外一个session无法做ddl操作

B、基于session的临时表若在某一个sesson里面运行了事物，任何session都不运行做ddl包括运行该事物的session

4、解决ORA-14450错误

--查找sid及serial#

SQL> SELECTDISTINCT a.sid,a.SERIAL#FROMv$session a,v$sql b,v$enqueue_lockc

  2   WHERE a.SID=c.SIDAND c.TYPE=‘TO‘AND a.MODULE_HASH=b.MODULE_HASHANDlower(b.SQL_TEXT)LIKE ‘%tmp_tab%‘;

    SID      SERIAL#

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

    191       264   

    40        166

--使用sys用户登陆

SQL> altersystem kill session ‘191,264‘;

System altered

SQL> altersystem kill session ‘40,166‘;

SQL>

--回到scott

SQL> ALTERTABLE temp_tabADD cNUMBER(2) ;

Table altered

ORA-14450 attempt to access a transactional temp table already in use,古老的榕树,5-wow.com