【Oracle】表領域（Tablespace）完全ガイド｜CREATE TABLESPACE・DATAFILE 追加・自動拡張・容量確認・UNDO/TEMP 管理まで解説

-- 永続表領域の作成（最もシンプルな形）
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/orcl/app_data01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL          -- ローカル管理表領域（推奨）
SEGMENT SPACE MANAGEMENT AUTO;   -- 自動セグメント領域管理（推奨）

-- AUTOEXTEND ON: データファイルを自動拡張する
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/orcl/app_data01.dbf'
    SIZE 500M                    -- 初期サイズ
    AUTOEXTEND ON                -- 自動拡張を有効にする
    NEXT 100M                    -- 1回の拡張サイズ
    MAXSIZE 10G;                 -- 最大サイズ（UNLIMITED にすると上限なし）

-- TEMP 表領域の作成（ソート・ハッシュ結合用の一時領域）
CREATE TEMPORARY TABLESPACE app_temp
TEMPFILE '/u01/oradata/orcl/app_temp01.dbf'
    SIZE 2G
    AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

-- UNDO 表領域の作成
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/oradata/orcl/undotbs02.dbf'
    SIZE 2G
    AUTOEXTEND ON NEXT 500M MAXSIZE 30G;

-- BIGFILE 表領域（データファイル1つのみ・最大 128TB まで対応）
CREATE BIGFILE TABLESPACE big_app_data
DATAFILE '/u01/oradata/orcl/big_app01.dbf'
    SIZE 10G AUTOEXTEND ON MAXSIZE UNLIMITED;

-- 表領域にデータファイルを追加する
ALTER TABLESPACE app_data
ADD DATAFILE '/u01/oradata/orcl/app_data02.dbf'
    SIZE 1G
    AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

-- TEMPFILE を追加する（TEMP 表領域の場合）
ALTER TABLESPACE app_temp
ADD TEMPFILE '/u01/oradata/orcl/app_temp02.dbf'
    SIZE 2G AUTOEXTEND ON NEXT 500M;

-- 既存のデータファイルのサイズを拡張する
ALTER DATABASE DATAFILE '/u01/oradata/orcl/app_data01.dbf' RESIZE 2G;

-- 既存データファイルに AUTOEXTEND を設定する（事後に変更する場合）
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf'
    AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- AUTOEXTEND を OFF にする
ALTER DATABASE DATAFILE '/u01/oradata/orcl/users01.dbf' AUTOEXTEND OFF;

-- 表領域をオフライン / オンラインにする（メンテナンス時）
ALTER TABLESPACE app_data OFFLINE NORMAL;   -- 正常にオフライン（完全チェックポイント実行後）
ALTER TABLESPACE app_data ONLINE;

-- 表領域を削除する（データも一緒に削除）
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;
-- INCLUDING CONTENTS: 表領域内のオブジェクトを削除
-- AND DATAFILES: OS 上のデータファイルも削除（省略するとファイルが残る）

-- 表領域の合計サイズ・使用量・空き容量・使用率を一覧表示する
SELECT
    df.tablespace_name,
    df.total_mb,
    NVL(fs.free_mb, 0)                            AS free_mb,
    df.total_mb - NVL(fs.free_mb, 0)              AS used_mb,
    ROUND((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb * 100, 1) AS used_pct,
    df.autoextend,
    df.max_mb
FROM (
    SELECT
        tablespace_name,
        ROUND(SUM(bytes) / 1048576)          AS total_mb,
        MAX(DECODE(autoextensible, 'YES', 'YES', 'NO')) AS autoextend,
        ROUND(SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) / 1048576) AS max_mb
    FROM DBA_DATA_FILES
    GROUP BY tablespace_name
) df
LEFT JOIN (
    SELECT tablespace_name, ROUND(SUM(bytes) / 1048576) AS free_mb
    FROM DBA_FREE_SPACE
    GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
ORDER BY used_pct DESC;

-- TEMP 表領域の使用状況（TEMPFILE は DBA_TEMP_FILES を使う）
SELECT
    tt.tablespace_name,
    ROUND(SUM(tf.bytes) / 1048576)   AS total_mb,
    ROUND(SUM(tu.bytes_used) / 1048576) AS used_mb
FROM DBA_TABLESPACES tt
JOIN DBA_TEMP_FILES tf ON tt.tablespace_name = tf.tablespace_name
LEFT JOIN V$TEMP_SPACE_HEADER tu ON tf.file_id = tu.file#
WHERE tt.contents = 'TEMPORARY'
GROUP BY tt.tablespace_name;

-- データファイルの詳細を確認する
SELECT
    file_name,
    tablespace_name,
    ROUND(bytes / 1048576)    AS size_mb,
    ROUND(maxbytes / 1048576) AS max_mb,
    autoextensible
FROM DBA_DATA_FILES
ORDER BY tablespace_name, file_id;

-- ユーザー作成時にクォータを設定する
CREATE USER app_user IDENTIFIED BY "Pass123#"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE app_temp
QUOTA 500M ON app_data          -- app_data 表領域を最大 500MB まで使用可能
QUOTA UNLIMITED ON app_index;   -- app_index 表領域は無制限

-- 既存ユーザーのクォータを変更する
ALTER USER app_user QUOTA 2G ON app_data;
ALTER USER app_user QUOTA UNLIMITED ON app_data;
ALTER USER app_user QUOTA 0 ON app_data;   -- クォータを 0 に設定（新規オブジェクト作成不可）

-- ユーザーのクォータ状況を確認する
SELECT username, tablespace_name,
       ROUND(bytes_used / 1048576, 1)    AS used_mb,
       ROUND(max_bytes / 1048576, 1)     AS quota_mb,
       DECODE(max_bytes, -1, 'UNLIMITED', TO_CHAR(ROUND(max_bytes / 1048576, 1)) || ' MB') AS quota
FROM DBA_TS_QUOTAS
WHERE username = 'APP_USER';

-- ORA-01536 が発生した場合: 該当ユーザーと表領域を確認する
-- ORA-01536: space quota exceeded for tablespace 'APP_DATA'
-- → ALTER USER app_user QUOTA UNLIMITED ON app_data; で解決

-- RESOURCE ROLE を付与すると UNLIMITED TABLESPACE システム権限が付与される（全表領域無制限）
-- ※ UNLIMITED TABLESPACE 権限は強力なので本番環境では慎重に
GRANT UNLIMITED TABLESPACE TO app_user;   -- 全表領域で無制限
-- または RESOURCE ロール（UNLIMITED TABLESPACE を含む）を付与する
GRANT RESOURCE TO app_user;

-- 現在の UNDO 表領域を確認する
SHOW PARAMETER undo_tablespace;
-- → VALUE: UNDOTBS1

-- UNDO 表領域を切り替える（新しい UNDO 表領域が作成済みの場合）
ALTER SYSTEM SET undo_tablespace = UNDOTBS2;

-- UNDO_RETENTION: ロールバック情報を保持する最低時間（秒）
SHOW PARAMETER undo_retention;
-- → デフォルト: 900（15分）
ALTER SYSTEM SET undo_retention = 3600;   -- 1時間に延長（フラッシュバック用途など）

-- UNDO の使用状況を確認する
SELECT
    status,
    ROUND(SUM(bytes) / 1048576, 1) AS mb,
    COUNT(*) AS segments
FROM DBA_UNDO_EXTENTS
GROUP BY status;
-- ACTIVE: 現在のトランザクションで使用中
-- UNEXPIRED: UNDO_RETENTION 保持期間内（まだ解放できない）
-- EXPIRED: 解放可能（新しいトランザクションが必要なら上書きされる）

-- ORA-01555（snapshot too old）が発生する場合
-- → UNDO_RETENTION を延長するか、UNDO 表領域に RETENTION GUARANTEE を設定する
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;  -- EXPIRED を上書きしない（容量に注意）
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE; -- デフォルト（必要に応じて EXPIRED を上書き）