【ERROR】ORA-14452: ... temporary table already in use

一、问题描述

        通常我们在删除临时表时会遇到的ORA-14452报错问题，首先我们先了解一下什么是临时表（temporary table），顾名思义就是临时使用，使用完自动清理。按要清理条件分为两种：1.事务 2.会话。而且临时表中存入的数据对当前会话是私有的，每个会话只能读取使用自己的数据。

二、官方给出报错信息

ORACLE 给出报错代码信息：

1. [oracle@test ~]$ oerr ora 14452
   
2. 14452, 00000, "attempt to create, alter or drop an index on temporary table already in use"
   
3. // *Cause: An attempt was made to create, alter or drop an index on temporary
   
4. // table which is already in use.
   
5. // *Action: All the sessions using the session-specific temporary table have
   
6. // to truncate table and all the transactions using transaction
   
7. // specific temporary table have to end their transactions.

三、实验

（1） 事务级 临时表

会话一：

1.创建基于事务的临时表

1. SCOTT@OCM11G >create global temporary table sam_tmp (name varchar2(10)) on commit delete rows;
   
2. 
   
3. Table created.

2.插入测试数据

1. SCOTT@OCM11G >insert into sam_tmp values ('sam');
   
2. 
   
3. 1 row created.

3.检查数据

1. SCOTT@OCM11G >select * from sam_tmp;
   
2. 
   
3. NAME
   
4. ----------
   
5. sam

会话二：

1.删除全局临时表时报错ORA-14452

1. SCOTT@OCM11G >drop table sam_tmp;
   
2. drop table sam_tmp
   
3.            *
   
4. ERROR at line 1:
   
5. ORA-14452: attempt to create, alter or drop an index on temporary table already
   
6. in use

小结：会话一结束事务后，会话二才能够删除全局临时表。也还可以通过查询哪个用户持有sam_tmp表锁，将会话kill掉后再drop table 即可。下面是会话级别的temporary table  我们需要将会话kill掉才能够删除全局临时表。

（2） 会话级 临时表

会话一：

1.创建基于会话的临时表

1. SCOTT@OCM11G >create global temporary table sam_tmp1 (name varchar2(10)) ON COMMIT PRESERVE ROWS;
   
2. 
   
3. Table created.

2.插入测试数据

1. SCOTT@OCM11G >insert into sam_tmp1 values ('suzzy');
   
2. 
   
3. 1 row created.

3.检查数据

1. SCOTT@OCM11G >select * from sam_tmp1;
   
2. 
   
3. NAME
   
4. ----------
   
5. suzzy

4.提交数据

1. SCOTT@OCM11G >commit;
   
2. 
   
3. Commit complete.

会话二：

1.删除全局临时表时报错ORA-14452

1. SCOTT@OCM11G >drop table sam_tmp1;
   
2. drop table sam_tmp1
   
3.            *
   
4. ERROR at line 1:
   
5. ORA-14452: attempt to create, alter or drop an index on temporary table already
   
6. in use

2.会话三中将sid=237  serial#=29 的会话kill后，可以成功drop table。 还有一种情况，可以drop table成功，那就是会话一将表中数据truncate，注意delete 加 commit; 是不成的。 必须 truncate 。 这个小例子就留给各位朋友自己测试喽。

1. SCOTT@OCM11G >drop table sam_tmp1;
   
2. 
   
3. Table dropped.

会话三：

1.查询出谁锁住了对象 SAM_TMP1

1. SYS@OCM11G >SELECT SID, SERIAL# FROM V$SESSION V WHERE sid in (select sid from v$lock l, dba_objects o WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER('SAM_TMP1'));
   
2. 
   
3.        SID SERIAL#
   
4. ---------- ----------
   
5.        237 29

2.KILL锁住对象SAM_TMP1临时表的连接

1. SYS@OCM11G >ALTER SYSTEM KILL SESSION '237, 29';
   
2. 
   
3. System altered.

小结：会话一结束会话或者truncate临时表后，才可以drop 这张临时表。 当然让连接的用户自己断开连接也不太可能，这时DBA可以采用alter system kill session 'sid, serial#’ ;  来kill 锁住临时表的会话。

四、总结

        临时表这个东东，看来只有DBA们会用，其实开发人员也应该多多学习一下它，会解决开发很多问题。自己去用程序控制临时表，费时费力，不如直接使用ORACLE提供的，很高效，安全。再回顾一下，temporary table : There are two mode , 1. for transaction 2 for session .  Let’s study more oracle technology include dba and developer.     Where there is a will, there is a way.  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26148431/viewspace-2135552/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26148431/viewspace-2135552/