1、在删除用户时不能顺利进行，出现ora-14452错误 SQLgt; drop user ggtransfer2 cascade; drop user ggtransfer2 cascade * ER
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
