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

-- 세션과 프로세스 매핑 현황 조회
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.server,
       s.program,
       s.machine,
       p.spid        AS os_pid,
       s.logon_time,
       s.last_call_et AS idle_seconds
FROM   v$session s
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.type = 'USER'
ORDER BY s.last_call_et DESC;

-- Shared Server 환경에서 디스패처 상태 확인
SELECT name,
       status,
       messages,
       bytes,
       breaks,
       accept
FROM   v$dispatcher;

-- 문제가 되는 세션 강제 종료 (DBA 권한 필요)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- 예시: SID=125, SERIAL#=3467인 세션 종료
ALTER SYSTEM KILL SESSION '125,3467' IMMEDIATE;

-- STATUS가 KILLED이거나 오랫동안 INACTIVE 상태인 세션 조회
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.osuser,
       s.machine,
       p.spid AS os_process_id,
       s.last_call_et AS idle_time_sec
FROM   v$session s
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.status IN ('KILLED', 'INACTIVE')
  AND  s.username IS NOT NULL
  AND  s.last_call_et > 3600  -- 1시간 이상 유휴 상태
ORDER BY s.last_call_et DESC;

-- PADDR이 있지만 실제 OS 프로세스가 없는 고아 세션 식별
-- (이 쿼리는 paddr이 v$process에 없는 세션을 찾음)
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.paddr
FROM   v$session s
WHERE  s.paddr NOT IN (SELECT addr FROM v$process)
  AND  s.username IS NOT NULL;

-- OS 레벨에서 프로세스 확인 후 정리 (Linux/Unix 기준)
-- SQL*Plus에서 spid를 확인 후 OS에서 직접 kill
-- 1단계: Oracle에서 세션 종료 시도
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

-- 2단계: 위 명령이 효과 없으면 IMMEDIATE 옵션 사용
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

-- spid 확인
SELECT p.spid, s.sid, s.serial#, s.username
FROM   v$process p
JOIN   v$session s ON p.addr = s.paddr
WHERE  s.sid = &target_sid;

-- 현재 진행 중인 XA/분산 트랜잭션 확인
SELECT local_tran_id,
       global_tran_id,
       state,
       mixed,
       advice,
       tran_comment
FROM   dba_2pc_pending;

-- 오래된 인-더브트(in-doubt) 트랜잭션 강제 커밋 또는 롤백
-- 주의: 반드시 애플리케이션 팀과 협의 후 실행할 것

-- 강제 커밋
COMMIT FORCE 'local_tran_id';

-- 강제 롤백
ROLLBACK FORCE 'local_tran_id';

-- 예시
ROLLBACK FORCE '1.23.456';

-- 세션 전환 관련 DBMS_SESSION 사용 예시 (올바른 패턴)
-- Attach 전 반드시 현재 세션 상태 확인
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO   v_count
  FROM   v$session
  WHERE  audsid = SYS_CONTEXT('USERENV','SESSIONID')
    AND  status  = 'ACTIVE';

  IF v_count > 0 THEN
    -- 안전하게 세션 작업 수행
    DBMS_OUTPUT.PUT_LINE('세션 정상 상태 확인됨');
  ELSE
    DBMS_OUTPUT.PUT_LINE('세션 상태 이상 - 관리자 확인 필요');
  END IF;
END;
/

-- 유휴 세션 자동 종료 Profile 생성
CREATE PROFILE limited_session_profile LIMIT
  IDLE_TIME          30    -- 30분 유휴 시 세션 종료
  CONNECT_TIME       480   -- 최대 8시간 연결 유지
  SESSIONS_PER_USER  10    -- 사용자당 최대 10개 세션
  FAILED_LOGIN_ATTEMPTS 5;

-- 해당 Profile을 사용자에게 적용
ALTER USER app_user PROFILE limited_session_profile;

-- 현재 Profile 설정 확인
SELECT username, profile
FROM   dba_users
WHERE  username = 'APP_USER';

-- Profile 상세 설정 확인
SELECT resource_name, limit
FROM   dba_profiles
WHERE  profile = 'LIMITED_SESSION_PROFILE'
  AND  resource_type = 'KERNEL';

-- 모니터링용 뷰 생성 (DBA가 주기적으로 조회)
CREATE OR REPLACE VIEW v_session_health AS
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.server,
       s.machine,
       s.program,
       p.spid              AS os_pid,
       s.last_call_et      AS idle_sec,
       s.logon_time,
       CASE
         WHEN p.spid IS NULL AND s.paddr != '00' THEN 'ORPHAN_SESSION'
         WHEN s.status = 'KILLED'               THEN 'KILLED_SESSION'
         WHEN s.last_call_et > 7200             THEN 'LONG_IDLE'
         ELSE 'NORMAL'
       END AS health_status
FROM   v$session s
LEFT JOIN v$process p ON s.paddr = p.addr
WHERE  s.type = 'USER';

-- 비정상 세션만 필터링하여 조회
SELECT * FROM v_session_health
WHERE  health_status != 'NORMAL'
ORDER BY idle_sec DESC;