ORA-14452

今天在删除临时表的时候出现如下报错信息：

点击(此处)折叠或打开

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

之前该表的建表语句如下：

点击(此处)折叠或打开

1. SQL> create global temporary table sys_event_session
   
2.   2 on commit preserve rows
   
3.   3 as
   
4.   4 select *
   
5.   5 from v$system_event
   
6.   6 where 1 = 0;
   
7. 
   
8. Table created.
   
9. 
   
10. SQL> insert into sys_event_session
    
11.   2 select * from v$system_event;

在linux环境下执行:oerr ora 14452  显示如下：

点击(此处)折叠或打开

1. [oracle@centos5 ~]$ 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.

通过Action不难发现，只需要在drop table tablename之前执行truncate table tablename即可。如下：

点击(此处)折叠或打开

1. SQL> truncate table sys_event_session;
   
2. 
   
3. Table truncated.
   
4. 
   
5. SQL> drop table sys_event_session;
   
6. 
   
7. Table dropped.

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

转载于:http://blog.itpub.net/29173997/viewspace-1126224/