如何解决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';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29551564/viewspace-2126658/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29551564/viewspace-2126658/