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

-- 1단계: 현재 활성 세션 목록 실시간 조회
SELECT sid, serial#, username, status, machine, program, sql_id
FROM   v$session
WHERE  status = 'ACTIVE'
  AND  username IS NOT NULL
ORDER BY sid;

-- 2단계: 특정 세션 존재 여부 확인 후 KILL
DECLARE
    v_sid     NUMBER := 123;   -- 종료하려는 SID
    v_serial  NUMBER := 4567;  -- 해당 SERIAL#
    v_count   NUMBER;
BEGIN
    -- 세션이 여전히 존재하는지 확인
    SELECT COUNT(*)
    INTO   v_count
    FROM   v$session
    WHERE  sid     = v_sid
      AND  serial# = v_serial;

    IF v_count > 0 THEN
        EXECUTE IMMEDIATE 
            'ALTER SYSTEM KILL SESSION ''' || v_sid || ',' || v_serial || ''' IMMEDIATE';
        DBMS_OUTPUT.PUT_LINE('세션 종료 완료: SID=' || v_sid || ', SERIAL#=' || v_serial);
    ELSE
        DBMS_OUTPUT.PUT_LINE('세션이 이미 종료되었습니다. SID=' || v_sid);
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -22 THEN
            DBMS_OUTPUT.PUT_LINE('ORA-00022: 세션이 이미 종료되었거나 유효하지 않습니다.');
        ELSE
            RAISE;
        END IF;
END;
/

-- 단일 인스턴스: 정확한 SID와 SERIAL# 조회
SELECT sid, 
       serial#, 
       username, 
       status,
       osuser,
       machine,
       TO_CHAR(logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
FROM   v$session
WHERE  username = 'APPUSER'   -- 특정 사용자 세션 필터링
  AND  status   != 'KILLED';

-- 단일 인스턴스 세션 종료
ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

-- RAC 환경: INST_ID 포함하여 세션 조회
SELECT inst_id, sid, serial#, username, status, machine
FROM   gv$session
WHERE  username IS NOT NULL
  AND  status   != 'KILLED'
ORDER BY inst_id, sid;

-- RAC 환경 세션 종료 (INST_ID 포함)
ALTER SYSTEM KILL SESSION '123,4567,@2' IMMEDIATE;
-- 형식: 'SID, SERIAL#, @INST_ID'

-- 특정 SQL을 실행 중인 세션 찾아서 종료 준비
SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       q.sql_text
FROM   gv$session s
JOIN   gv$sql     q ON s.sql_id = q.sql_id
                    AND s.inst_id = q.inst_id
WHERE  q.sql_text LIKE '%특정키워드%';

-- SQL Trace 활성화 전 세션 유효성 검증 포함 프로시저
CREATE OR REPLACE PROCEDURE safe_enable_trace (
    p_sid    IN NUMBER,
    p_serial IN NUMBER
)
AS
    v_count NUMBER;
BEGIN
    -- 세션 존재 여부 사전 확인
    SELECT COUNT(*)
    INTO   v_count
    FROM   v$session
    WHERE  sid     = p_sid
      AND  serial# = p_serial
      AND  status != 'KILLED';

    IF v_count = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 
            'SID=' || p_sid || ', SERIAL#=' || p_serial || ' 세션이 존재하지 않습니다.');
    END IF;

    -- SQL Trace 활성화
    DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(
        sid    => p_sid,
        serial => p_serial,
        sql_trace => TRUE
    );

    DBMS_OUTPUT.PUT_LINE('SQL Trace 활성화 완료: SID=' || p_sid);

EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -22 THEN
            DBMS_OUTPUT.PUT_LINE('ORA-00022: 세션 추적 중 세션이 종료되었습니다.');
        ELSE
            DBMS_OUTPUT.PUT_LINE('에러 발생: ' || SQLERRM);
            RAISE;
        END IF;
END safe_enable_trace;
/

-- 사용 예시
EXEC safe_enable_trace(123, 4567);

-- 장시간 실행 세션 자동 정리 스크립트 예시 (재발 방지용)
BEGIN
    FOR r IN (
        SELECT sid, serial#
        FROM   v$session
        WHERE  status   = 'ACTIVE'
          AND  username IS NOT NULL
          AND  last_call_et > 3600  -- 1시간 이상 실행 중인 세션
    ) LOOP
        BEGIN
            EXECUTE IMMEDIATE 
                'ALTER SYSTEM KILL SESSION ''' 
                || r.sid || ',' || r.serial# || ''' IMMEDIATE';
        EXCEPTION
            WHEN OTHERS THEN
                IF SQLCODE IN (-22, -30) THEN
                    -- ORA-00022 또는 ORA-00030: 이미 종료된 세션, 무시하고 진행
                    NULL;
                ELSE
                    RAISE;
                END IF;
        END;
    END LOOP;
END;
/