ORA-14452:attempt to create,alter or drop an index on temporary table already in

背景:
今天删除用户时提示以下错误：
SQL>  drop user MDSYS cascade;
 drop user MDSYS cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

查看报错说明：

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

试图创建，更改或删除正在使用的临时表中的索引

解决方法：
使用下面语句查出对应的seesion，然后kill。
select 'alter system kill session '''||sid||','||serial#||'''' from v$session where sid in (
select sid from v$lock where id1 in (
select object_id from dba_objects where object_name in (
select table_name from dba_tables where owner=' MDSYS'))); 

example：

1. SQL> select 'alter system kill session '''||sid||','||serial#||'''' from v$session where sid in (
   
2.   2 select sid from v$lock where id1 in (
   
3.   3 select object_id from dba_objects where object_name in (
   
4.   4 select table_name from dba_tables where owner='MDSYS')));
   
5. 
   
6. 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''''
   
7. -------------------------------------------------------------------------------------------------------------
   
8. alter system kill session '578,23189'
   
9. 
   
10. 1 row selected.
    
11. 
    
12. SQL> alter system kill session '578,23189';
    
13. 
    
14. System altered.

kill seesion后再次删除成功
SQL> drop user MDSYS cascade; 

User dropped.

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

转载于:http://blog.itpub.net/205377/viewspace-1769125/