Q:oracle锁表如何处理
阅读原文时间:2023年07月10日阅读:1

有时候在drop表或者其他对象的时候,会遇到ORA-00054:资源正忙,要求指定NOWAIT(中文字符集)或者ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired错误(英文字符集),Oracle官方解决方案:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Cause: Interested resource is busy.

Action: Retry if necessary or increase timeout.

通常处理方法有好几个步骤:

  • 查看数据库中那些用户产生了锁

select username,sid,serial#,logon_time from v$locked_object,v$session where v$locked_object.session_id=v$session.sid;

  • 根据SID查看具体的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;

  • 如果短时间内此SQL语句无法完成,且急需重建索引,那么kill此session

alter system kill session '2095,51898';

以上步骤太过繁琐,这里有一段代码的解决方案:

BEGIN
FOR C IN (SELECT S.SID SID, S.SERIAL# SERIAL FROM V$LOCKED_OBJECT L, V$SESSION S WHERE L.SESSION_ID = S.SID) LOOP
EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION ''' || C.SID || ',' || C.SERIAL || '''');
END LOOP;
END;

手机扫一扫

移动阅读更方便

阿里云服务器
腾讯云服务器
七牛云服务器