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