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

-- 현재 임시 테이블 잠금을 보유한 세션 조회
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;

-- 장시간 열린 트랜잭션 조회 (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;

-- 현재 스키마의 전역 임시 테이블 목록 조회
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;  -- 트랜잭션 종료 시 자동 데이터 삭제 (잠금 해제 유도)

-- 모니터링용 임계치 확인 쿼리 (임시 테이블 잠금이 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';