如何解决ORA-00054资源正忙，要求指定NOWAIT
1.通过上句查找出已被锁定的数据库表及相关的sid、serial#及spid
select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
2.在数据库中灭掉相关session
alter system kill session 'sid,serial#';--sid及serial#为第一步查出来的数据
SQL> select username,sid,serial#,logon_time from v$locked_object,v$session where v$locked_object.session_id=v$session.sid;
USERNAME SID SERIAL# LOGON_TIME
------------------------------ ---------- ---------- -----------
NEWCCS 1491 11152 2010-7-6 17
2.根据SID查看具体的SQL语句
SQL> select sql_text from v$session,v$sqltext_with_newlines where decode(v$session.sql_hash_value,0,prev_hash_value,sql_hash_value)=v$sqltext_with_newlines.hash_value and v$session.sid=&sid order by piece;
3.如果短时间内此SQL语句无法完成，且急需重建索引，那么kill此session
SQL> alter system kill session '1491,11152';
select object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;
2.在数据库中灭掉相关session
alter system kill session 'sid,serial#';--sid及serial#为第一步查出来的数据
SQL> select username,sid,serial#,logon_time from v$locked_object,v$session where v$locked_object.session_id=v$session.sid;
USERNAME SID SERIAL# LOGON_TIME
------------------------------ ---------- ---------- -----------
NEWCCS 1491 11152 2010-7-6 17
2.根据SID查看具体的SQL语句
SQL> select sql_text from v$session,v$sqltext_with_newlines where decode(v$session.sql_hash_value,0,prev_hash_value,sql_hash_value)=v$sqltext_with_newlines.hash_value and v$session.sid=&sid order by piece;
3.如果短时间内此SQL语句无法完成，且急需重建索引，那么kill此session
SQL> alter system kill session '1491,11152';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29551564/viewspace-2126658/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
扩充linux swap空间
请登录后发表评论
登录
全部评论
