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

간만에 오라클 에러 ..

 

프로시저 디버깅 하다 보니 락이 걸렸는지 다음 메시지 출력

 

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

 

락 풀어야 해서 찾은 인터넷에 퍼가기 막아놓은 쿼리 수준

SELECT A.SID
    , A.SERIAL#
    , A.STATUS
FROM V$SESSION A
    ,V$LOCK B
    ,DBA_OBJECTS C
WHERE A.SID = B.SID
    AND B.ID1 = C.OBJECT_ID
    AND B.TYPE = 'TM'
    AND C.OBJECT_NAME = '{테이블 명}'
;

 

오라클 툴 제공하는 쿼리 수준

with vs as (select rownum rnum,
                      inst_id,
                      sid,
                      serial#,
                      status,
                      username,
                      last_call_et,
                      command,
                      machine,
                      osuser,
                      module,
                      action,
                      resource_consumer_group,
                      client_info,
                      client_identifier,
                      type,
                      terminal,
                      sql_id,
                      sql_child_number
                 from gv$session) 
     select vs.inst_id, vs.sid ,serial# serial, vs.sql_id, vs.sql_child_number,
            vs.username "Username",
            case when vs.status = 'ACTIVE' 
                      then last_call_et 
                 else null end "Seconds in Wait",
            (select command_name from v$sqlcommand where command_type = vs.command ) "Command",
            vs.machine "Machine",
            vs.osuser "OS User", 
            lower(vs.status) "Status",
            vs.module "Module",
            vs.action "Action",
            vs.resource_consumer_group,
            vs.client_info,
            vs.client_identifier
       from vs 
      where vs.USERNAME is not null
        and nvl(vs.osuser,'x') <> 'SYSTEM'
        and vs.type <> 'BACKGROUND'
        order by 1,2,3

 

처리 방법 #1 - Lock Time out 시간 설정

alter session set ddl_lock_timeout = 600;

 

처리 방법 #2 - 직접 프로세스 삭제(중지)

ALTER SYSTEM KILL SESSION 'sid, serial#';