[ORACLE] ORA-00054 오류 완전 정복 : 리소스 잠금으로 인한 DDL 실패의 근본 원인과 실무 대응 전략

[ORACLE] ORA-00054

 

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

Oracle 데이터베이스에서 ORA-00054 오류는 흔히 테이블이나 인덱스에 대한 DDL (ALTER, DROP, RENAME) 작업 수행 시 발생합니다. 이는 대상 객체가 현재 다른 세션에서 사용 중이어서, 요청한 작업이 즉시 수행될 수 없다는 것을 의미합니다. 이 글에서는 ORA-00054 오류가 발생하는 구조적 이유부터, 진단 쿼리, 해결 방법, 그리고 실무에서 꼭 알아야 할 회피 전략까지 다각도로 설명합니다.

---

1. ORA-00054 오류란?

이 오류는 Oracle에서 다음과 같은 상황에서 발생합니다:

ALTER TABLE employees ADD (email VARCHAR2(100));
-- 또는
DROP INDEX emp_name_idx;

위 명령이 실행되었을 때 대상 테이블이나 인덱스가 현재 다른 세션에서 DML(INSERT, UPDATE, DELETE) 또는 커서 유지 중이라면, Oracle은 리소스 잠금 상태라고 판단하여 다음 오류를 반환합니다:

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

---

2. 주요 원인과 대응 전략 요약

원인	설명	대응 방안
세션이 테이블 점유	DML 실행 중인 세션이 테이블을 잠금	락 보유 세션 확인 후 종료 또는 대기
커서가 열린 상태	PL/SQL에서 OPEN CURSOR 유지 중	커서 종료 유도 또는 세션 kill
NOWAIT 옵션 사용	잠금 상태에 대한 대기 없이 즉시 실패	WAIT 또는 DBMS_LOCK 사용으로 대기 가능
스케줄된 배치 작업 간 충돌	ETL 작업과 DDL이 겹칠 경우	작업 시간 분리 또는 동기화

---

3. 실무 사례 분석: 마트 시스템 배치 충돌

배경: 대형 마트의 POS 시스템에서 매일 3시경 통계 테이블 인덱스를 DROP 후 REBUILD하는 스크립트가 오류 발생.

에러 로그:

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

원인 분석:

• 고객 포인트 적립 프로세스가 아직 종료되지 않은 상태에서 배치 실행
• 리소스를 잠근 상태에서 DDL 시도 → 실패

해결:

1. 락 보유 세션 확인 및 종료 유도
2. 스케줄 재조정 (DDL 30분 후로 지연)
3. 스크립트에서 FORCE 또는 WAIT 옵션 도입

---

4. 잠금 세션 확인 SQL

잠금 보유 세션 찾기

SELECT l.session_id, s.username, s.osuser, s.program, l.locked_mode,
       s.machine, s.status
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid
WHERE o.object_name = 'EMPLOYEES';

특정 SID 세션 종료

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
-- 예: ALTER SYSTEM KILL SESSION '120,54879' IMMEDIATE;

---

5. 해결 방법 요약

방법	설명	적용 주의사항
세션 강제 종료	락 보유 세션 종료	업무 영향 가능성 있음
DDL 스케줄 조정	비업무 시간으로 이동	충돌 방지 가능
DBMS_LOCK 사용	사용자 정의 잠금 컨트롤	PL/SQL 내 구현 필요
WAIT 옵션 활용	DDL 스크립트에서 일정 시간 대기 설정	10g 이상부터 제한적 사용 가능

---

6. 실무 팁: 자동 회피 로직 구현

배치 스크립트 또는 DBA 관리 툴에서 다음과 같은 방식으로 자동화하는 것이 좋습니다:

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE employees ADD (temp_flag NUMBER)';
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('리소스가 바쁨. 5분 후 재시도');
    DBMS_LOCK.SLEEP(300);
    -- 재시도 로직 추가 가능
END;

이와 같은 방식은 시스템 운영 자동화에서 안정성을 향상시키는 데 크게 기여합니다.

---

7. 예방 전략

• 업무 시간 외의 유지보수 시간 확보
• 자동화 스크립트에 LOCK 대기 또는 회피 로직 포함
• 락 보유 가능성이 있는 세션에 대한 사전 모니터링
• PL/SQL 커서 종료 여부 주기적 점검

---

8. 결론

ORA-00054 오류는 단순한 시스템 오류가 아니라, 리소스 관리와 트랜잭션 동시성 설계에 대한 메시지입니다. 실무에서는 이 오류를 반복적으로 경험하게 되며, DDL 시점 관리와 커넥션 제어, 자동화된 락 회피 전략이 필수적입니다. 이 글을 통해 ORA-00054의 발생 원리를 깊이 이해하고, 실무 대응력을 한층 끌어올릴 수 있기를 바랍니다.

---

출처

• Oracle® Database Error Messages 19c
• Oracle DBA Performance Tuning Guide