【Oracle】表領域の使用状況を確認する SQL まとめ｜使用率・AUTOEXTEND・TEMP/UNDO・閾値アラート・増加傾向まで解説

-- 表領域ごとの合計サイズ / 使用サイズ / 空きサイズ / 使用率
SELECT
    df.tablespace_name,
    ROUND(df.total_mb, 1) AS total_mb,
    ROUND(df.total_mb - NVL(fs.free_mb, 0), 1) AS used_mb,
    ROUND(NVL(fs.free_mb, 0), 1) AS free_mb,
    ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS used_pct
FROM
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
     FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
     FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;

-- AUTOEXTEND ON のファイルは maxbytes を上限として使用率を計算
SELECT
    df.tablespace_name,
    ROUND(df.current_mb, 1) AS current_mb,
    ROUND(df.max_mb, 1) AS max_mb,
    ROUND(df.current_mb - NVL(fs.free_mb, 0), 1) AS used_mb,
    ROUND((df.current_mb - NVL(fs.free_mb, 0)) / df.max_mb * 100, 1)
        AS used_pct_of_max
FROM
    (SELECT tablespace_name,
            SUM(bytes)/1024/1024 AS current_mb,
            SUM(CASE WHEN autoextensible = 'YES'
                     THEN GREATEST(maxbytes, bytes)
                     ELSE bytes END)/1024/1024 AS max_mb
     FROM dba_data_files
     GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
     FROM dba_free_space
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct_of_max DESC;

-- Oracle が AUTOEXTEND を考慮して計算してくれる
SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 1) AS used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 1) AS max_mb,
       ROUND(used_percent, 1) AS used_pct
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

-- used_space / tablespace_size はブロック単位
-- 8192 = デフォルトのブロックサイズ（8KB）
-- ブロックサイズが異なる場合は DB_BLOCK_SIZE を確認

SELECT
    df.tablespace_name,
    df.file_name,
    ROUND(df.bytes/1024/1024, 1) AS file_mb,
    ROUND((df.bytes - NVL(fs.free_bytes, 0))/1024/1024, 1) AS used_mb,
    ROUND(NVL(fs.free_bytes, 0)/1024/1024, 1) AS free_mb,
    df.autoextensible AS autoext,
    ROUND(df.maxbytes/1024/1024, 1) AS max_mb
FROM dba_data_files df
LEFT JOIN (
    SELECT file_id, SUM(bytes) AS free_bytes
    FROM dba_free_space
    GROUP BY file_id
) fs ON df.file_id = fs.file_id
ORDER BY df.tablespace_name, df.file_name;

-- TEMP 表領域の使用率
SELECT
    tablespace_name,
    ROUND(tablespace_size/1024/1024, 1) AS total_mb,
    ROUND(allocated_space/1024/1024, 1) AS alloc_mb,
    ROUND(free_space/1024/1024, 1) AS free_mb,
    ROUND(allocated_space/tablespace_size * 100, 1) AS used_pct
FROM dba_temp_free_space;

-- TEMP を使用しているセッション別の内訳
SELECT
    s.sid, s.serial#, s.username,
    ROUND(t.blocks * 8192/1024/1024, 1) AS temp_mb,
    s.sql_id,
    SUBSTR(q.sql_text, 1, 80) AS sql_text
FROM v$sort_usage t
JOIN v$session s ON t.session_addr = s.saddr
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
ORDER BY t.blocks DESC;

-- UNDO 表領域の使用率（ステータス別）
SELECT
    tablespace_name,
    status,
    ROUND(SUM(bytes)/1024/1024, 1) AS mb
FROM dba_undo_extents
GROUP BY tablespace_name, status
ORDER BY tablespace_name, status;
-- status: ACTIVE（使用中）/ UNEXPIRED（保持中）/ EXPIRED（再利用可能）

-- UNDO の残り保持時間
SHOW PARAMETER undo_retention;
-- undo_retention = 900（秒）→ 15 分間の UNDO を保持

-- 実質使用率（AUTOEXTEND 考慮）が 80% を超える表領域
SELECT tablespace_name,
       ROUND(used_percent, 1) AS used_pct
FROM dba_tablespace_usage_metrics
WHERE used_percent >= 80
ORDER BY used_percent DESC;

-- 残り容量ベースでの閾値チェック
SELECT
    df.tablespace_name,
    ROUND(NVL(fs.free_mb, 0), 1) AS free_mb,
    ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS used_pct
FROM
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
     FROM dba_data_files GROUP BY tablespace_name) df
LEFT JOIN
    (SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
     FROM dba_free_space GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
WHERE NVL(fs.free_mb, 0) < 1024  -- 1GB 未満
ORDER BY free_mb;

-- 過去 7 日間の表領域使用量の推移（AWR スナップショット）
SELECT
    TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD') AS day,
    ts.tablespace_id,
    (SELECT tablespace_name FROM v$tablespace WHERE ts# = ts.tablespace_id) AS ts_name,
    ROUND(MAX(ts.tablespace_usedsize * 8192)/1024/1024, 1) AS used_mb
FROM dba_hist_tbspc_space_usage ts
JOIN dba_hist_snapshot sn ON ts.snap_id = sn.snap_id
WHERE sn.begin_interval_time >= SYSDATE - 7
GROUP BY TO_CHAR(sn.begin_interval_time, 'YYYY-MM-DD'), ts.tablespace_id
ORDER BY ts_name, day;

-- 閾値超過の表領域をアラートテーブルに記録するプロシージャ
CREATE OR REPLACE PROCEDURE check_tablespace_usage IS
    v_threshold NUMBER := 80;  -- 閾値: 80%
BEGIN
    FOR rec IN (
        SELECT tablespace_name,
               ROUND(used_percent, 1) AS used_pct
        FROM dba_tablespace_usage_metrics
        WHERE used_percent >= v_threshold
    ) LOOP
        INSERT INTO tablespace_alerts
            (tablespace_name, used_pct, check_date)
        VALUES
            (rec.tablespace_name, rec.used_pct, SYSDATE);
    END LOOP;
    COMMIT;
END;
/

-- DBMS_SCHEDULER で日次実行
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name   => 'JOB_CHECK_TABLESPACE',
        job_type   => 'PLSQL_BLOCK',
        job_action => 'BEGIN check_tablespace_usage; END;',
        start_date => TRUNC(SYSDATE) + 1 + 8/24,  -- 明日 8:00
        repeat_interval => 'FREQ=DAILY; BYHOUR=8',
        enabled    => TRUE
    );
END;
/