ITPub博客

首页 > 数据库 > Oracle > ORA-14452:attempt to create,alter or drop an index on temporary table already in

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

原创 Oracle 作者:yangi1314 时间:2015-08-12 11:55:19 0 删除 编辑
背景:
今天删除用户时提示以下错误:
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. 'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''''
  6. -------------------------------------------------------------------------------------------------------------
  7. alter system kill session '578,23189'

  8. 1 row selected.

  9. SQL> alter system kill session '578,23189';

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

User dropped.



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

请登录后发表评论 登录
全部评论

注册时间:2013-07-11

  • 博文量
    63
  • 访问量
    200232