【Oracle】データファイルを追加・変更する方法｜ALTER TABLESPACE ADD DATAFILE・リサイズ・移動・削除・TEMP ファイルまで解説

-- 表領域にデータファイルを追加
ALTER TABLESPACE users
    ADD DATAFILE '/oracle/oradata/ORCL/users02.dbf'
    SIZE 1G;

-- AUTOEXTEND ON で自動拡張を有効化
ALTER TABLESPACE users
    ADD DATAFILE '/oracle/oradata/ORCL/users03.dbf'
    SIZE 500M
    AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- AUTOEXTEND ON MAXSIZE UNLIMITED（ディスク上限まで拡張）
ALTER TABLESPACE users
    ADD DATAFILE '/oracle/oradata/ORCL/users04.dbf'
    SIZE 500M
    AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

-- ASM: ディスクグループ名で指定
ALTER TABLESPACE users
    ADD DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
-- ファイル名は ASM が自動生成

-- OMF 有効時: ファイル名を省略可能
ALTER TABLESPACE users ADD DATAFILE SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;
-- db_create_file_dest に設定されたパスにファイルが自動作成される

-- OMF の設定確認
SHOW PARAMETER db_create_file_dest;

-- データファイルの一覧（サイズ / AUTOEXTEND / 最大サイズ）
SELECT tablespace_name, file_name,
       ROUND(bytes/1024/1024) AS size_mb,
       autoextensible AS autoext,
       ROUND(maxbytes/1024/1024) AS max_mb,
       ROUND(increment_by * 8192/1024/1024) AS next_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;

-- データファイルごとの使用率
SELECT df.tablespace_name, df.file_name,
       ROUND(df.bytes/1024/1024) AS file_mb,
       ROUND((df.bytes - NVL(fs.free_bytes, 0))/1024/1024) AS used_mb,
       ROUND(NVL(fs.free_bytes, 0)/1024/1024) AS free_mb,
       df.autoextensible AS autoext
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, file_name,
       ROUND(bytes/1024/1024) AS size_mb,
       autoextensible AS autoext,
       ROUND(maxbytes/1024/1024) AS max_mb
FROM dba_temp_files
ORDER BY tablespace_name;

-- 既存ファイルを 2GB に拡大
ALTER DATABASE DATAFILE '/oracle/oradata/ORCL/users01.dbf' RESIZE 2G;

-- ファイル番号で指定する場合
ALTER DATABASE DATAFILE 4 RESIZE 2G;

-- 使用中のブロックより小さくはできない
ALTER DATABASE DATAFILE '/oracle/oradata/ORCL/users01.dbf' RESIZE 500M;
-- ORA-03297: ファイルは要求されたサイズより大きいデータを含んでいます
-- → 使用中の HWM（High Water Mark）以下には縮小不可

-- AUTOEXTEND を ON にする（NEXT と MAXSIZE を指定）
ALTER DATABASE DATAFILE '/oracle/oradata/ORCL/users01.dbf'
    AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

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

-- MAXSIZE を変更
ALTER DATABASE DATAFILE '/oracle/oradata/ORCL/users01.dbf'
    AUTOEXTEND ON NEXT 200M MAXSIZE 32G;

-- TEMP 表領域に TEMPFILE を追加
ALTER TABLESPACE temp
    ADD TEMPFILE '/oracle/oradata/ORCL/temp02.dbf'
    SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- TEMPFILE のリサイズ
ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf' RESIZE 2G;

-- TEMPFILE の AUTOEXTEND を変更
ALTER DATABASE TEMPFILE '/oracle/oradata/ORCL/temp01.dbf'
    AUTOEXTEND ON NEXT 100M MAXSIZE 5G;

-- 12c 以降: オンラインで移動可能
ALTER DATABASE MOVE DATAFILE
    '/oracle/oradata/ORCL/users01.dbf'
    TO '/oracle/new_disk/users01.dbf';
-- DB 稼働中に実行可能（ダウンタイムなし）

-- (1) 表領域を OFFLINE
ALTER TABLESPACE users OFFLINE;

-- (2) OS でファイルをコピー
-- $ cp /oracle/oradata/ORCL/users01.dbf /oracle/new_disk/users01.dbf

-- (3) 制御ファイルのパスを更新
ALTER DATABASE RENAME FILE
    '/oracle/oradata/ORCL/users01.dbf'
    TO '/oracle/new_disk/users01.dbf';

-- (4) 表領域を ONLINE
ALTER TABLESPACE users ONLINE;

-- 11g 以降: 空のデータファイルを削除
ALTER TABLESPACE users DROP DATAFILE '/oracle/oradata/ORCL/users04.dbf';

-- 注意: データが入っているファイルは削除できない
-- ORA-03262: the file is non-empty
-- → テーブルを MOVE で別ファイルに移動してからDROP

ORA-01653: unable to extend table HR.EMPLOYEES by 128 in tablespace USERS

-- BIGFILE 表領域の作成（データファイルは 1 つだけ）
CREATE BIGFILE TABLESPACE big_data
    DATAFILE '/oracle/oradata/ORCL/big_data01.dbf'
    SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 100G;

-- BIGFILE は ADD DATAFILE できない（1 ファイル固定）
-- リサイズでサイズを変更する
ALTER DATABASE DATAFILE '/oracle/oradata/ORCL/big_data01.dbf' RESIZE 50G;

-- ORA-01653 が発生 → 緊急でデータファイルを追加
ALTER TABLESPACE users
    ADD DATAFILE '/oracle/oradata/ORCL/users05.dbf'
    SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

-- 追加後の確認
SELECT file_name, ROUND(bytes/1024/1024) AS size_mb, autoextensible
FROM dba_data_files WHERE tablespace_name = 'USERS';

-- I/O 分散のため、新しいディスクにデータファイルを追加
ALTER TABLESPACE app_data
    ADD DATAFILE '/oracle/disk2/app_data02.dbf'
    SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

-- 既存テーブルを新しいファイルに移動
ALTER TABLE orders MOVE TABLESPACE app_data;
-- Oracle が空きのあるデータファイルに自動配置

-- ORA-01652（TEMP で拡張不可）対処
ALTER TABLESPACE temp
    ADD TEMPFILE '/oracle/oradata/ORCL/temp02.dbf'
    SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

-- AUTOEXTEND OFF のファイルを検出（容量不足リスク）
SELECT tablespace_name, file_name,
       ROUND(bytes/1024/1024) AS size_mb
FROM dba_data_files
WHERE autoextensible = 'NO'
ORDER BY tablespace_name;

-- AUTOEXTEND ON にする一括 SQL を生成
SELECT 'ALTER DATABASE DATAFILE ''' || file_name || ''' AUTOEXTEND ON NEXT 100M MAXSIZE 10G;'
FROM dba_data_files
WHERE autoextensible = 'NO';