【Oracle】セッションの確認・強制切断方法完全ガイド｜V$SESSION・ロック調査・ALTER SYSTEM KILL SESSION

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.osuser,
    s.machine,
    s.program,
    s.logon_time,
    s.last_call_et AS last_call_sec
FROM
    v$session s
ORDER BY
    s.logon_time;

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
    s.last_call_et AS last_call_sec
FROM
    v$session s
WHERE
    s.username IS NOT NULL
ORDER BY
    s.logon_time;

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.wait_class,
    s.seconds_in_wait,
    q.sql_text
FROM
    v$session s
    LEFT JOIN v$sql q ON s.sql_id = q.sql_id
                      AND s.sql_child_number = q.child_number
WHERE
    s.status = 'ACTIVE'
    AND s.username IS NOT NULL
ORDER BY
    s.seconds_in_wait DESC;

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    ROUND(s.last_call_et / 60, 1)    AS elapsed_min,
    s.wait_class,
    s.event,
    s.seconds_in_wait
FROM
    v$session s
WHERE
    s.username IS NOT NULL
    AND s.last_call_et > 1800        -- 1800秒 = 30分
ORDER BY
    s.last_call_et DESC;

SELECT
    l.sid,
    l.type,
    l.id1,
    l.id2,
    l.lmode,          -- 保持しているロックモード（2=RS, 3=RX, 4=S, 5=SRX, 6=X）
    l.request,        -- 要求しているロックモード（0=なし、>0=待機中）
    l.block,          -- 1=他のセッションをブロックしている
    l.ctime           -- このロック状態になってからの秒数
FROM
    v$lock l
WHERE
    l.type IN ('TM', 'TX')  -- TM=テーブルロック, TX=トランザクションロック
ORDER BY
    l.block DESC, l.ctime DESC;

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    s.last_call_et AS elapsed_sec
FROM
    v$session s
WHERE
    s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.blocking_session,       -- このセッションをブロックしているSID
    s.seconds_in_wait,
    s.wait_class,
    s.event
FROM
    v$session s
WHERE
    s.blocking_session IS NOT NULL;

SELECT
    lo.session_id                    AS sid,
    s.serial#,
    s.username,
    s.machine,
    o.owner,
    o.object_name,
    o.object_type,
    lo.locked_mode,                  -- 2=RS, 3=RX, 4=S, 5=SRX, 6=X
    s.status
FROM
    v$locked_object lo
    JOIN v$session   s ON lo.session_id = s.sid
    JOIN dba_objects  o ON lo.object_id  = o.object_id
ORDER BY
    o.object_name;

SELECT
    b.sid                            AS blocker_sid,
    b.serial#                        AS blocker_serial,
    b.username                       AS blocker_user,
    b.machine                        AS blocker_machine,
    b.program                        AS blocker_program,
    b.last_call_et                   AS blocker_elapsed_sec,
    w.sid                            AS waiter_sid,
    w.serial#                        AS waiter_serial,
    w.username                       AS waiter_user,
    w.seconds_in_wait                AS waiter_wait_sec,
    o.owner || '.' || o.object_name  AS locked_object
FROM
    v$session w
    JOIN v$session      b  ON w.blocking_session = b.sid
    JOIN v$locked_object lo ON lo.session_id = b.sid
    JOIN dba_objects      o  ON lo.object_id  = o.object_id
ORDER BY
    waiter_wait_sec DESC;

-- 構文: ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
ALTER SYSTEM KILL SESSION '123,456';

-- 通常（ロールバック後に切断）
ALTER SYSTEM KILL SESSION '123,456';

-- 即時切断（ロールバック中でも強制終了）
ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

-- DISCONNECT SESSION（ネットワーク接続も即時切断）
ALTER SYSTEM DISCONNECT SESSION '123,456' IMMEDIATE;

-- 特定ユーザーの全セッションに対するKILLコマンドを生成
-- CHR(39)は単一引用符（'）を表す
SELECT
    'ALTER SYSTEM KILL SESSION ' || CHR(39) || sid || ',' || serial# || CHR(39) || ' IMMEDIATE;'
        AS kill_command
FROM
    v$session
WHERE
    username = 'APPUSER'          -- KILLしたいユーザー名に変更
    AND status != 'KILLED';       -- すでにKILL済みは除外

-- 初期化パラメータの確認
SELECT name, value, description
FROM   v$parameter
WHERE  name IN ('processes', 'sessions', 'transactions');

-- 現在の接続セッション数（ユーザーセッションのみ）
SELECT COUNT(*) AS current_sessions
FROM   v$session
WHERE  username IS NOT NULL;

-- ステータス別の内訳
SELECT status, COUNT(*) AS cnt
FROM   v$session
WHERE  username IS NOT NULL
GROUP BY status
ORDER BY cnt DESC;

-- 現在の値を確認
SHOW PARAMETER processes;
SHOW PARAMETER sessions;

-- SPFILEを使って変更（再起動後に有効）
ALTER SYSTEM SET processes  = 500 SCOPE = SPFILE;
ALTER SYSTEM SET sessions   = 555 SCOPE = SPFILE;

-- 変更後は再起動が必要
SHUTDOWN IMMEDIATE;
STARTUP;

-- 反映確認
SHOW PARAMETER processes;
SHOW PARAMETER sessions;

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    ROUND(s.last_call_et / 3600, 1) AS idle_hours,
    TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time
FROM
    v$session s
WHERE
    s.username IS NOT NULL
    AND s.status = 'INACTIVE'
    AND s.last_call_et > 3600        -- 3600秒 = 1時間以上アイドル
ORDER BY
    s.last_call_et DESC;

-- アラートログのパスを確認
SELECT value FROM v$parameter WHERE name = 'background_dump_dest';

-- または診断ディレクトリから確認
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- セッションA                      セッションB
-- ───────────────────              ────────────────────
UPDATE tbl SET col=1 WHERE id=1;   UPDATE tbl SET col=2 WHERE id=2;
                                   UPDATE tbl SET col=2 WHERE id=1; -- Aが保持するidのロックを待つ
UPDATE tbl SET col=1 WHERE id=2;   -- Bが保持するidのロックを待つ → デッドロック

SELECT sid, serial#, username, status
FROM   v$mystat
WHERE  statistic# = 0;

-- または
SELECT sys_context('USERENV', 'SID') AS my_sid FROM dual;

SELECT
    t.addr,
    t.xidusn,
    t.xidslot,
    t.xidsqn,
    t.status,
    t.used_ublk * 8192 / 1024          AS undo_kb,
    t.used_urec                         AS undo_records,
    s.sid,
    s.serial#,
    s.username,
    ROUND(t.start_date - SYSDATE, 4)   AS start_offset_day
FROM
    v$transaction t
    JOIN v$session s ON t.addr = s.taddr
ORDER BY
    undo_kb DESC;