IT技术博客

最近在业务测试环境中，系统报了一个ORA-14450的错误

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

首先根据百度的信息说是临时表在未提交时，又有另外的会话在进行DDL操作

场景模拟:

1.开启一个会话

 SQL> insert into angu_tmp values('TY');
 
 1 row inserted

--不进行事务的回滚和提交

2.开启另一个会话

SQL> alter table ANGU_TMP add Test varchar2(10);
alter table ANGU_TMP add Test varchar2(10)
ORA-14450: attempt to access a transactional temp table already in use

错误代码确实重现了，问题是业务测试环境怎么可能有DDL操作呢？

继续百度，发现大多数的文章都是互相抄袭的(如果用Google效果肯能会好一些)，都说是DDL操作引起的。

看下Oracle的官方文档时怎么说的吧：

ORA-14450: 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 the concurrent transaction has committed or aborted.

大意是试图访问一个在同一个会话中已经被某个事务已经占据了的临时表。

在一个会话中会有独立的事务，应该是开启自治事务。

场景模拟：

SQL> insert  into angu_tmp values('YT');
 
1 row inserted
 
SQL>
SQL> DECLARE
  2  PRAGMA AUTONOMOUS_TRANSACTION;
  3   BEGIN
  4  insert  into angu_tmp values('YT');
  5  COMMIT;
  6  END;
  7  /
 
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
insert  into angu_tmp values('YT');
COMMIT;
END;
 
ORA-14450: attempt to access a transactional temp table already in use
ORA-06512: at line 5

果然这才是根本的原因，找到的原因，后面就可以根据具体的业务解决了。