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

今早遇到一位网友，要给一个临时表添加字段的时候报ORA-14450: attempt to access a transactional temp table already in use错误

其实遇到这种错误，肯定是这个表被其他session占用，如其他session正在对这个表DML，这种情况通过查询V$LOCKED_OBJECT试图，关联上V$session,一般就能查到是什么session在操作这张表了，然后要么kill掉，要么roll或者commit就可以

不巧这位网友遇到的恰恰是一张session级别的临时表，就算session使用完并且commit或者roll了事务， 如果这个session没有退出，那么它还是会占用这资源，你想在其他session操作DDL，还是会报ORA-14450错误

我们模拟一下环境：

如果是事务级的临时表：

Session A:

SQL> set time on
14:01:53 SQL> create global temporary table t_tmp_tab (id number,name varchar2(20)) on commit delete rows;
Table created.
14:01:56 SQL> insert into t_tmp_tab values(1,'GOOLEN');
1 row created.
14:02:00 SQL> 

Session B:

14:04:17 SQL> select b.Object_Name, Session_Id, Process, Locked_Mode
14:04:18   2  FROM v$locked_Object a, Dba_Objects b
14:04:18   3  WHERE a.Object_Id = b.Object_Id
14:04:18   4  and b.object_name= 'T_TMP_TAB';

OBJECT_NAME                         SESSION_ID PROCESS                  LOCKED_MODE
----------------------------------- ---------- ------------------------ -----------
T_TMP_TAB                                   34 23796                              3

此时是可以查到操作这张表的session的

如果session A 提交或者回退了事务

14:02:00 SQL> roll
Rollback complete.
14:06:19 SQL> 

那么session A已经释放对T_TMP_TAB的资源占用，此时session B是可以对T_TMP_TAB这个表做DDL的

14:04:18 SQL> select b.Object_Name, Session_Id, Process, Locked_Mode
14:06:34   2  FROM v$locked_Object a, Dba_Objects b
14:06:34   3  WHERE a.Object_Id = b.Object_Id
14:06:34   4  and b.object_name= 'T_TMP_TAB';
no rows selected
14:09:59 SQL> alter table t_tmp_tab add(email varchar2(20));
Table altered.
14:10:10 SQL> 

而如果换成session级的临时表的话，就算session A已经提交了事务，但是此session没有退出的话，在session B做DDL操作也会报错，如下：

Session A:

14:06:19 SQL> create global temporary table s_tab_tmp (id number,name varchar2(20)) on commit preserve rows;
Table created.
14:12:58 SQL> insert into s_tab_tmp values(1,'GOOLEN');
1 row created.
14:13:07 SQL> commit;
Commit complete.
--session A上的事务已经提交

Session B:

14:13:15 SQL> select b.Object_Name, Session_Id, Process, Locked_Mode
14:13:29   2  FROM v$locked_Object a, Dba_Objects b
14:13:29   3  WHERE a.Object_Id = b.Object_Id
14:13:29   4  and b.object_name= 'S_TAB_TMP';
no rows selected
--sesion B也查不到相关的信息
-- 但是对其DDL操作还是会抛错
14:13:40 SQL> alter table s_tab_tmp add(email varchar2(20));
alter table s_tab_tmp add(email varchar2(20))
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

只有当session A退出后，相应的资源才能释放，其他session也才能进行操作

session A：

14:17:31 SQL> exit

session B：

14:13:55 SQL> alter table s_tab_tmp add(email varchar2(20));

Table altered.

--此时session B已经可以操作DDL.

这时候问题来了，如果一个session使用完这个临时表后，session一直存在不退出怎么办，要知道一套系统不止是dba一个人在使用

其实我们可以查询v$enqueue_lock，找到type为TO 的session，处理掉这些session就可以

14:21:43 SQL> select sid from v$enqueue_lock t where t.type='TO';

       SID
----------
        34

TO        Temp Object

从v$lock里面也是可以查到的

14:22:06 SQL> select sid from v$lock t where t.type='TO';

       SID
----------
        34