linux 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 sooner0b1331709591d260c1c78e86d0c51c18.png

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值