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