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

-- KILLED 상태의 세션 확인
SELECT sid,
       serial#,
       username,
       status,
       osuser,
       machine,
       program,
       sql_id,
       last_call_et
FROM   v$session
WHERE  status = 'KILLED'
ORDER  BY last_call_et DESC;

-- 세션과 연결된 OS 프로세스 정보 조회
SELECT s.sid,
       s.serial#,
       s.status,
       s.username,
       p.spid AS os_pid
FROM   v$session s
JOIN   v$process p ON s.paddr = p.addr
WHERE  s.status = 'KILLED';

-- (참고용 Shell 명령 - SQL이 아닌 OS 명령어)
-- kill -9 [os_pid]

-- Oracle 레벨에서 즉시 연결 해제 방법 (DBA 권한 필요)
ALTER SYSTEM DISCONNECT SESSION '&sid,&serial#' IMMEDIATE;

-- 락 경합 현황 조회
SELECT l.sid,
       s.serial#,
       s.username,
       s.status,
       l.type,
       l.lmode,
       l.request,
       o.object_name,
       o.object_type
FROM   v$lock l
JOIN   v$session s ON l.sid = s.sid
LEFT   JOIN dba_objects o ON l.id1 = o.object_id
WHERE  l.request > 0
   OR  (l.lmode > 0 AND l.type IN ('TM', 'TX'))
ORDER  BY l.sid;

-- 블로킹 세션 분석 쿼리
SELECT  blocker.sid          AS blocking_sid,
        blocker.serial#      AS blocking_serial,
        blocker.username     AS blocking_user,
        blocker.status       AS blocking_status,
        waiter.sid           AS waiting_sid,
        waiter.serial#       AS waiting_serial,
        waiter.username      AS waiting_user,
        waiter.sql_id        AS waiting_sql_id,
        waiter.wait_class    AS wait_class,
        waiter.seconds_in_wait
FROM    v$session blocker
JOIN    v$session waiter ON blocker.sid = waiter.blocking_session
WHERE   waiter.blocking_session IS NOT NULL
ORDER   BY waiter.seconds_in_wait DESC;

-- 문제 세션 종료 (DBA 권한 필요)
ALTER SYSTEM KILL SESSION '&blocking_sid,&blocking_serial' IMMEDIATE;

-- 특정 세션에서 실행 중인 SQL 확인 후 종료 결정
SELECT sql_fulltext
FROM   v$sql
WHERE  sql_id = '&sql_id';

-- 사용자별 적용 프로파일 확인
SELECT username,
       profile
FROM   dba_users
WHERE  account_status = 'OPEN'
ORDER  BY username;

-- 프로파일 상세 리소스 제한 확인
SELECT profile,
       resource_name,
       resource_type,
       limit
FROM   dba_profiles
WHERE  profile IN (
           SELECT profile FROM dba_users WHERE username = '&target_user'
       )
ORDER  BY resource_type,
          resource_name;

-- 특정 프로파일의 IDLE_TIME 제한 완화 (분 단위)
ALTER PROFILE app_user_profile LIMIT
    IDLE_TIME    30
    CONNECT_TIME UNLIMITED;

-- DEFAULT 프로파일 확인 및 수정 (주의: 모든 사용자에게 영향)
ALTER PROFILE DEFAULT LIMIT
    IDLE_TIME UNLIMITED;

-- 변경 사항 즉시 반영 확인
SELECT profile,
       resource_name,
       limit
FROM   dba_profiles
WHERE  profile = 'APP_USER_PROFILE'
  AND  resource_name IN ('IDLE_TIME', 'CONNECT_TIME');

-- 커넥션 유효성 검증용 쿼리 (validationQuery로 활용)
SELECT 1 FROM dual;

-- 세션 상태 모니터링 자동화를 위한 뷰 생성 예시
CREATE OR REPLACE VIEW v_long_running_sessions AS
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et       AS elapsed_seconds,
       s.sql_id,
       q.sql_text
FROM   v$session s
LEFT   JOIN v$sql q ON s.sql_id = q.sql_id
WHERE  s.status = 'ACTIVE'
  AND  s.last_call_et > 300   -- 5분 이상 실행 중인 세션
  AND  s.username IS NOT NULL
ORDER  BY s.last_call_et DESC;

-- 정기 모니터링 스크립트 예시: 10분 이상 실행 중인 세션 감지
SELECT s.sid,
       s.serial#,
       s.username,
       s.osuser,
       s.machine,
       s.program,
       ROUND(s.last_call_et / 60, 1) AS running_minutes,
       s.sql_id,
       SUBSTR(q.sql_text, 1, 100)    AS sql_preview
FROM   v$session s
LEFT   JOIN v$sql q ON s.sql_id = q.sql_id
WHERE  s.type = 'USER'
  AND  s.status = 'ACTIVE'
  AND  s.last_call_et > 600
ORDER  BY s.last_call_et DESC;