Oracle ORA-00054 오류 원인과 해결 방법 완벽 가이드

-- 현재 락을 보유 중인 세션과 대기 중인 세션 조회
SELECT
    l.sid,
    l.serial#,
    l.username,
    l.status,
    l.osuser,
    l.machine,
    l.program,
    l.logon_time,
    o.object_name,
    o.object_type,
    l2.type        AS lock_type,
    l2.lmode       AS lock_mode,
    l2.request     AS request_mode
FROM
    v$session  l,
    v$lock     l2,
    dba_objects o
WHERE
    l.sid      = l2.sid
    AND l2.id1 = o.object_id
    AND l2.type = 'TM'
ORDER BY
    l2.lmode DESC;

-- 특정 테이블에 걸린 락을 보유한 세션의 SQL 확인
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    q.sql_text,
    s.last_call_et AS elapsed_seconds
FROM
    v$session  s,
    v$sqlarea  q,
    v$lock     l,
    dba_objects o
WHERE
    s.sql_id    = q.sql_id(+)
    AND s.sid   = l.sid
    AND l.id1   = o.object_id
    AND l.type  = 'TM'
    AND o.object_name = UPPER('&테이블명')
    AND l.lmode > 0;

-- 세션 강제 종료 (SID, SERIAL# 는 위 조회 결과 참조)
ALTER SYSTEM KILL SESSION '1234,56789' IMMEDIATE;

-- RAC 환경에서는 INST_ID 포함
ALTER SYSTEM KILL SESSION '1234,56789,@1' IMMEDIATE;

-- NOWAIT 대신 WAIT N 초 지정 (최대 N초간 대기 후 획득 실패 시 에러)
SELECT *
FROM   orders
WHERE  order_id = 1001
FOR UPDATE WAIT 10;  -- 10초 대기

-- LOCK TABLE에 WAIT 절 적용
LOCK TABLE orders IN EXCLUSIVE MODE WAIT 30;  -- 30초 대기

-- 세션 레벨에서 DDL 락 대기 시간 설정 (초 단위)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 60;

-- 이후 DDL 수행 시 최대 60초 대기 후 실행
ALTER TABLE orders ADD (memo VARCHAR2(200));

-- 시스템 레벨 설정 (영구 적용)
ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30 SCOPE = BOTH;

-- 현재 설정 확인
SHOW PARAMETER DDL_LOCK_TIMEOUT;

-- 오래된 미완료 트랜잭션 세션 조회 (30분 이상 경과)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    ROUND((SYSDATE - l.start_time) * 24 * 60, 2) AS elapsed_min,
    l.used_ublk,
    l.used_urec
FROM
    v$session     s,
    v$transaction l
WHERE
    s.taddr = l.addr
    AND (SYSDATE - l.start_time) * 24 * 60 > 30
ORDER BY
    elapsed_min DESC;

-- 락 체인(Lock Chain) 전체 구조 파악: 블로커와 웨이터 한눈에 확인
SELECT
    DECODE(l.block, 0, '대기중', 1, '락보유(블로커)', '기타') AS lock_status,
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.last_call_et,
    o.object_name
FROM
    v$lock     l,
    v$session  s,
    dba_objects o
WHERE
    l.sid  = s.sid
    AND l.id1 = o.object_id(+)
    AND l.type = 'TM'
ORDER BY
    l.block DESC, l.id1;

-- DDL 작업 전 활성 세션 사전 점검 스크립트
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.last_call_et AS elapsed_sec,
    t.start_time   AS tx_start
FROM
    v$session     s,
    v$transaction t
WHERE
    s.taddr = t.addr(+)
    AND s.username IS NOT NULL
    AND s.status   = 'ACTIVE'
ORDER BY
    elapsed_sec DESC;

-- DBMS_SCHEDULER를 이용한 정기 락 모니터링 잡 생성 예시
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_LOCK_MONITOR',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            BEGIN
                -- 10분 이상 락 보유 세션 감지 시 로그 테이블에 기록
                INSERT INTO dba_lock_log (sid, serial#, username, elapsed_min, log_time)
                SELECT s.sid, s.serial#, s.username,
                       ROUND((SYSDATE - t.start_time)*24*60, 2),
                       SYSDATE
                FROM   v$session s, v$transaction t
                WHERE  s.taddr = t.addr
                AND    (SYSDATE - t.start_time)*24*60 > 10;
                COMMIT;
            END;
        ',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
        enabled         => TRUE,
        comments        => '장기 락 세션 5분 주기 모니터링'
    );
END;
/