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

ORA-00057란?

ORA-00057은 Oracle 데이터베이스에서 임시 테이블(Temporary Table)에 대한 잠금(Lock) 수가 시스템에서 허용하는 최대 임계치를 초과했을 때 발생하는 에러입니다. 이 에러는 주로 다수의 세션이 동시에 전역 임시 테이블(Global Temporary Table, GTT)을 집중적으로 사용하거나, 단일 세션이 과도하게 많은 임시 테이블 잠금을 보유하고 있을 때 나타납니다. 운영 환경에서 갑작스럽게 발생하면 트랜잭션 처리가 중단되고 애플리케이션 장애로 이어질 수 있으므로 신속한 원인 파악과 대응이 필수적입니다.

---

주요 발생 원인

1. 과도한 동시 세션의 전역 임시 테이블 사용

Oracle의 전역 임시 테이블(GTT)은 세션 혹은 트랜잭션 단위로 데이터를 격리하여 저장하는 구조입니다. 수백 개 이상의 세션이 동시에 동일한 GTT에 접근하거나, 각 세션이 여러 GTT를 동시에 사용할 경우 내부적으로 관리되는 임시 테이블 잠금 카운터가 한계에 도달하여 ORA-00057이 발생합니다. 특히 커넥션 풀(Connection Pool) 환경에서 세션 수가 급증하는 배치 처리 시간대나 트래픽 피크 타임에 집중적으로 나타나는 패턴을 보입니다.

2. 트랜잭션 미종료로 인한 잠금 누적

애플리케이션 코드에서 COMMIT 또는 ROLLBACK 없이 트랜잭션이 장시간 열린 상태로 유지되면, 해당 세션이 보유한 임시 테이블 잠금이 해제되지 않고 계속 누적됩니다. 특히 예외 처리 로직이 미흡한 애플리케이션에서 오류 발생 후 세션이 좀비 상태(zombie session)로 남아있는 경우, 잠금이 무한정 유지되어 전체 시스템의 임시 테이블 잠금 가용 수를 모두 소진시키는 원인이 됩니다.

3. 잘못된 GTT 설계 및 남용

하나의 업무 로직 안에서 필요 이상으로 많은 전역 임시 테이블을 생성하거나, 영구 테이블(Permanent Table)로 처리해야 할 대용량 데이터를 무분별하게 GTT에 적재하는 설계 문제도 주요 원인입니다. GTT는 편리하지만 잠금 자원을 소비하는 객체이므로, 설계 단계에서 사용 수와 용도를 명확히 제한하지 않으면 운영 중 예기치 않게 ORA-00057 에러를 유발할 수 있습니다.

---

해결 방법

원인 1 해결 - 현재 임시 테이블 잠금 현황 파악 및 세션 정리

먼저 현재 어떤 세션이 얼마나 많은 임시 테이블 잠금을 보유하고 있는지 확인합니다.

-- 현재 임시 테이블 잠금을 보유한 세션 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    s.logon_time,
    COUNT(l.id1) AS temp_lock_count
FROM
    v$session s
    JOIN v$lock l ON s.sid = l.sid
WHERE
    l.type = 'TT'  -- TT: Temporary Table Lock
GROUP BY
    s.sid, s.serial#, s.username, s.status,
    s.machine, s.program, s.logon_time
ORDER BY
    temp_lock_count DESC;

-- 전체 임시 테이블 잠금 수 확인
SELECT
    COUNT(*) AS total_temp_locks,
    COUNT(DISTINCT sid) AS affected_sessions
FROM
    v$lock
WHERE
    type = 'TT';

과도한 잠금을 보유한 특정 세션을 강제 종료해야 할 경우 아래 쿼리를 사용합니다.

-- 문제 세션 강제 종료 (DBA 권한 필요)
-- 먼저 SID와 SERIAL# 확인 후 실행
ALTER SYSTEM KILL SESSION '1234,5678' IMMEDIATE;

-- 여러 좀비 세션을 일괄 처리하는 스크립트 생성
SELECT
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
        AS kill_statement
FROM
    v$session
WHERE
    status = 'INACTIVE'
    AND last_call_et > 3600  -- 1시간 이상 비활성 세션
    AND username IS NOT NULL;

원인 2 해결 - 미종료 트랜잭션 탐지 및 정리

오랫동안 열려 있는 트랜잭션을 찾아내어 조치합니다.

-- 장시간 열린 트랜잭션 조회 (1시간 이상)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    t.start_time,
    ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24, 2)
        AS elapsed_hours,
    t.used_ublk AS undo_blocks_used,
    t.log_io,
    t.phy_io
FROM
    v$transaction t
    JOIN v$session s ON t.addr = s.taddr
WHERE
    (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 > 1
ORDER BY
    elapsed_hours DESC;

-- GTT 사용 현황과 트랜잭션 상태를 함께 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    NVL(t.start_time, 'NO ACTIVE TXN') AS txn_start_time,
    l.type AS lock_type,
    COUNT(l.id1) OVER (PARTITION BY s.sid) AS lock_count
FROM
    v$session s
    LEFT JOIN v$transaction t ON s.taddr = t.addr
    LEFT JOIN v$lock l ON s.sid = l.sid AND l.type = 'TT'
WHERE
    l.type IS NOT NULL
ORDER BY
    s.sid;

원인 3 해결 - GTT 사용 현황 감사 및 재설계

현재 데이터베이스에 존재하는 GTT 전체 목록과 사용 빈도를 확인합니다.

-- 현재 스키마의 전역 임시 테이블 목록 조회
SELECT
    owner,
    table_name,
    duration,
    num_rows,
    last_analyzed
FROM
    dba_tables
WHERE
    temporary = 'Y'
ORDER BY
    owner, table_name;

-- GTT를 현재 사용 중인 세션 확인
SELECT
    s.sid,
    s.username,
    s.status,
    o.object_name AS temp_table_name,
    o.object_type
FROM
    v$session s
    JOIN v$open_cursor oc ON s.sid = oc.sid
    JOIN dba_objects o ON UPPER(oc.sql_text) LIKE '%' || o.object_name || '%'
WHERE
    o.temporary = 'Y'
    AND o.object_type = 'TABLE';

-- 불필요한 GTT가 있다면 삭제 전 데이터 정리 후 DROP
-- STEP 1: 세션 데이터 확인
SELECT COUNT(*) FROM my_temp_table;  -- 해당 세션 데이터 확인

-- STEP 2: GTT 삭제
DROP TABLE my_unnecessary_temp_table;

-- STEP 3: 필요하다면 더 효율적인 구조로 재생성
CREATE GLOBAL TEMPORARY TABLE new_temp_table (
    id          NUMBER,
    data        VARCHAR2(200),
    created_dt  DATE DEFAULT SYSDATE
)
ON COMMIT DELETE ROWS;  -- 트랜잭션 종료 시 자동 데이터 삭제 (잠금 해제 유도)

---

예방 방법

1. 세션 및 잠금 모니터링 자동화

운영 환경에서 주기적으로 임시 테이블 잠금 수를 모니터링하는 자동화 스크립트나 Oracle Enterprise Manager(OEM) 알림을 구성하여 임계치 도달 전에 선제 대응할 수 있는 체계를 갖추어야 합니다. 아래와 같은 모니터링 쿼리를 5~10분 주기로 스케줄링하여 일정 수치 이상이면 DBA에게 알람을 발송하도록 구성하는 것이 실무에서 효과적입니다.

-- 모니터링용 임계치 확인 쿼리 (임시 테이블 잠금이 100 이상이면 경고)
SELECT
    CASE
        WHEN COUNT(*) >= 100 THEN 'WARNING: Temp Lock 수 임계치 초과 - ' || COUNT(*) || '개'
        WHEN COUNT(*) >= 50  THEN 'CAUTION: Temp Lock 수 주의 수준 - ' || COUNT(*) || '개'
        ELSE 'NORMAL: Temp Lock 수 정상 - ' || COUNT(*) || '개'
    END AS lock_status,
    COUNT(*) AS current_temp_locks
FROM
    v$lock
WHERE
    type = 'TT';

2. 애플리케이션 레벨의 트랜잭션 관리 강화

GTT를 사용하는 모든 애플리케이션 코드에서 반드시 예외 처리 블록 안에 COMMIT 또는 ROLLBACK을 명시적으로 호출하도록 코딩 표준을 수립하고 코드 리뷰 프로세스를 통해 관리해야 합니다. 특히 커넥션 풀 환경에서는 커넥션 반환 전 트랜잭션 상태를 초기화하는 로직을 반드시 포함시켜야 하며, ON COMMIT DELETE ROWS 옵션을 가진 GTT를 우선 사용하여 트랜잭션 종료 시 잠금이 자동 해제되도록 설계하는 것이 중요합니다.

---

관련 에러

에러 코드	설명
ORA-00054	resource busy and acquire with NOWAIT specified - 잠금 경합이 심할 때 NOWAIT 옵션 사용 시 발생하며 ORA-00057과 함께 나타날 수 있음
ORA-00060	deadlock detected while waiting for resource - 여러 세션 간 잠금 순환 대기로 발생하는 교착 상태로, GTT 과다 사용 환경에서 동반 발생 가능
ORA-01555	snapshot too old - GTT 사용 중 UNDO 공간 부족으로 발생하며 장시간 미종료 트랜잭션 상황에서 ORA-00057과 함께 나타나기도 함
ORA-04031	unable to allocate shared memory - SGA 메모리 부족 에러로, 대규모 GTT 운용 환경에서 간접적으로 연관됨