Oracle的Ora-00031 错误

Oracle的Ora-00031 错误

1、在删除用户时不能顺利进行，出现ora-14452错误

SQL> drop user ggtransfer2 cascade;

drop user ggtransfer2 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

 

2、查找该用户的对象所在的id

select object_id from user_objects where object_name=upper('GGS_STICK');

 

3、根据id找到sid，

select * from v$lock where id1=123623

 

548

 

4、根据sid找到sid及其serial#

select * from v$session where sid=548

548,1 

5、查看该sid的SQL语句

SELECT  sql_text

  FROM v$sqltext a

  WHERE a.hash_value = (SELECT sql_hash_value

                          FROM v$session b

                        WHERE b.SID = '&sid')

ORDER BY piece ASC

1、如果SQL语句不影响系统运行，kill

SQL> alter system kill session '548,1';

alter system kill session '548,1'

*

ERROR at line 1:

ORA-00031: session marked for kill

6、该SQL语句session不能正常kill，查找在OS系统的进程

select spid, oSUSEr, s.program

from v$session s,v$process p

where s.paddr=p.addr and s.sid=548

1  29321  oraoms Oracle@CQXXDBS01 (Q002)

2、在OS系统kill进程

Kill -9 29321

ORA-00031: session marked for kill

Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.

Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner