レコードを削除して表領域を空けたのに、DB縮小リサイズが実施できない場合がある。
これは、一度DBにレコードが大量に挿入されると、DBはHWMとしてその最高値を保存し、この値がDBの領域として確保され続けるため。
リサイズするには、HWMを下げる必要がある。
CSVでデータを退避 → TRUNCATE → 再投入
が一番確実だが、データ(レコード)をいじりたくなければ、以下の手順でHWMの値だけを下げる。
Oracleデータファイル縮小手順
対象DB:Oracle(ASSM表領域)
目的:実データ量に対してデータファイルサイズが過大な場合に縮小する
前提条件
- DBA権限が必要
- 業務時間外に実施すること(テーブルMOVE中は排他ロックが発生するため)
- 対象表領域のデータファイルパスを事前に把握しておくこと
凡例
- <データファイルパス>:対象データファイルのフルパス
例)/opt/oracle/oradata/XE/xxxx.dbf
- <スキーマ名>:対象スキーマ(ユーザー)名
- <目標サイズ>:縮小後のファイルサイズ(例:650M)
- <目標ブロック番号>:目標サイズに相当するブロック番号
計算式:目標サイズ(MB)× 1024 ÷ 8
例)650MB → 650 × 1024 ÷ 8 = 83200
STEP 1. 後方ブロックの使用状況を確認する
目標サイズより後方にあるセグメントを洗い出す。
SELECT
segment_name,
segment_type,
block_id,
blocks,
ROUND((block_id + blocks - 1) * 8 / 1024, 1) AS 末尾位置_MB
FROM dba_extents
WHERE file_id = (
SELECT file_id
FROM dba_data_files
WHERE file_name = '<データファイルパス>'
)
AND block_id > <目標ブロック番号>
ORDER BY block_id DESC;
確認ポイント
- 0件であればSTEP 5(リサイズ)へ直接進める
- セグメントが存在する場合、SEGMENT_TYPEを確認する
- TABLE → STEP 2(テーブルMOVE)が必要
- INDEX → STEP 3(インデックスREBUILD)が必要
- INDEX PARTITION → STEP 3(パーティションREBUILD)が必要
STEP 2. テーブルを前方へ移動する(MOVE)
STEP 1でTABLEが存在した場合に実施する。
ALTER TABLE <スキーマ名>.<テーブル名> MOVE;
注意
- MOVE中はテーブルに排他ロックがかかる(業務時間外に実施)
- MOVE後は関連インデックスがUNUSABLEになる(STEP 3が必須)
- LOB列を含む場合は追加オプションが必要な場合がある
STEP 3. INDEX(REBUILD)
STEP 2のMOVE後、またはSTEP 1でINDEXが存在した場合に実施する。
通常インデックス
ALTER INDEX <スキーマ名>.<インデックス名> REBUILD;
パーティションインデックス
-- パーティション名を先に確認する
SELECT partition_name
FROM dba_ind_partitions
WHERE index_owner = '<スキーマ名>'
AND index_name = '<インデックス名>'
ORDER BY partition_position;
-- パーティションごとにREBUILDする
ALTER INDEX <スキーマ名>.<インデックス名> REBUILD PARTITION <パーティション名>;
UNUSABLEインデックスの確認
SELECT index_name, status
FROM dba_indexes
WHERE owner = '<スキーマ名>'
AND status = 'UNUSABLE';
→ 0件になるまでREBUILDを続ける。
補足
- REBUILDを繰り返しても同じ後方ブロックに再配置される場合がある(Oracleの空き領域管理により、最近解放されたブロックが優先されるため)
- その場合はSTEP 4(主キーDROP&再作成)で対処する
STEP 4. 主キーをDROPして再作成する(REBUILDで解決しない場合)
STEP 3を繰り返しても後方にインデックスが残る場合に実施する。
4-1. 事前確認:外部キー参照チェック
主キーを参照する外部キーがあるとDROPがエラーになるため、事前に確認する。
SELECT
a.table_name AS 参照元テーブル,
a.constraint_name AS FK名,
a.r_constraint_name AS 参照先PK名
FROM dba_constraints a
WHERE a.owner = '<スキーマ名>'
AND a.constraint_type = 'R'
AND a.r_constraint_name IN ('<PK制約名>');
-- → 0件であれば安全にDROPできる
4-2. 事前確認:主キーカラムの重複・NULLチェック
再作成時に重複・NULLがあると失敗するため、DROP前に必ず確認する。
— 単一カラムの場合
SELECT <PKカラム名>, COUNT(*)
FROM <スキーマ名>.<テーブル名>
GROUP BY <PKカラム名>
HAVING COUNT(*) > 1 OR <PKカラム名> IS NULL;
— 複合主キーの場合
SELECT <カラム1>, <カラム2>, ..., COUNT(*)
FROM <スキーマ名>.<テーブル名>
GROUP BY <カラム1>, <カラム2>, ...
HAVING COUNT(*) > 1
OR <カラム1> IS NULL
OR <カラム2> IS NULL
OR ...;
— → 0件であれば再作成は必ず成功する
4-3. 事前確認:インデックス・制約の状態を記録する(比較用)
DROP前の状態を記録しておき、再作成後と比較する。
SELECT
c.constraint_name,
c.table_name,
c.status,
c.validated,
cc.column_name,
cc.position
FROM dba_constraints c
JOIN dba_cons_columns cc
ON c.owner = cc.owner
AND c.constraint_name = cc.constraint_name
WHERE c.owner = '<スキーマ名>'
AND c.constraint_name IN ('<PK制約名>', ...)
ORDER BY c.constraint_name, cc.position;
SELECT
index_name,
table_name,
uniqueness,
status,
partitioned
FROM dba_indexes
WHERE owner = '<スキーマ名>'
AND index_name IN ('<PK制約名>', ...)
ORDER BY index_name;
4-4. 主キー制約をDROPする
ALTER TABLE <スキーマ名>.<テーブル名> DROP PRIMARY KEY;
4-5. 後方ブロックが空になったか確認する
STEP 1と同じSQLを実行し、0件になることを確認する。
4-6. リサイクルビンの確認と削除
DROPしたインデックスがリサイクルビンに残っていると、ORA-03297が発生する。
-- 確認
SELECT
owner,
original_name,
object_name,
type,
ROUND(space * 8 / 1024, 1) AS 使用_MB
FROM dba_recyclebin
WHERE owner = '<スキーマ名>'
ORDER BY space DESC;
-- 削除(ユーザー指定)
PURGE TABLESPACE <表領域名> USER <スキーマ名>;
-- 削除(全ユーザー一括)
PURGE DBA_RECYCLEBIN;
STEP 5. データファイルをリサイズする
後方ブロックが0件になったことを確認してから実施する。
-- 最後尾ブロックを確認して余裕を持たせたサイズを設定する
SELECT
ROUND(MAX(block_id + blocks - 1) * 8 / 1024, 1) AS 最小リサイズ_MB
FROM dba_extents
WHERE file_id = (
SELECT file_id
FROM dba_data_files
WHERE file_name = '<データファイルパス>'
);
-- リサイズ実行(最小リサイズ_MBより大きい値を指定すること)
ALTER DATABASE DATAFILE '<データファイルパス>' RESIZE <目標サイズ>;
注意
- 最後尾ブロックより小さいサイズを指定するとORA-03297が発生する
- リサイズはデータファイルの現在サイズ以下に縮小する操作のみ対象
- AUTOEXTENSIBLE=YESの場合もRESIZEは有効
STEP 6. 主キー制約を再作成する(STEP 4を実施した場合)
通常テーブル(非パーティション)の場合
ALTER TABLE <スキーマ名>.<テーブル名>
ADD CONSTRAINT <PK制約名> PRIMARY KEY (<カラム1>, <カラム2>, ...);
パーティションテーブルの場合
- パーティションキーを確認する
SELECT column_name, column_position
FROM dba_part_key_columns
WHERE owner = '<スキーマ名>'
AND name = '<テーブル名>';
パーティションキーが主キーに含まれていれば、LOCALで再作成できる。
ALTER TABLE <スキーマ名>.<テーブル名>
ADD CONSTRAINT <PK制約名>
PRIMARY KEY (<カラム1>, <カラム2>, ...)
USING INDEX LOCAL;
STEP 7. 再作成後の確認
DROP前に記録した状態(STEP 4-3)と一致しているか確認する。
SELECT
c.constraint_name,
c.table_name,
c.status,
c.validated,
cc.column_name,
cc.position
FROM dba_constraints c
JOIN dba_cons_columns cc
ON c.owner = cc.owner
AND c.constraint_name = cc.constraint_name
WHERE c.owner = '<スキーマ名>'
AND c.constraint_name IN ('<PK制約名>', ...)
ORDER BY c.constraint_name, cc.position;
SELECT
index_name,
table_name,
uniqueness,
status,
partitioned
FROM dba_indexes
WHERE owner = '<スキーマ名>'
AND index_name IN ('<PK制約名>', ...)
ORDER BY index_name;
確認ポイント
- STATUS
- 通常インデックス → VALID
- パーティションインデックス → N/A
- VALIDATED:VALIDATEDであること
- PARTITIONED:DROP前と一致していること(パーティションテーブルはYES)
インシデント
ORA-03297:ファイルには使用中のデータが含まれています
原因1:dba_extentsに表示されないブロックが使用中(リサイクルビン等)
→ STEP 4-6のリサイクルビン削除を実施してリトライ
原因2:目標サイズが最後尾ブロックより小さい
→ STEP 5の最小リサイズ確認SQLで最後尾を確認してからリトライ
REBUILDを繰り返しても後方ブロックに再配置される
Oracleの空き領域管理により、最近解放されたブロックが優先的に再利用される。
→ STEP 4(主キーDROP&再作成)に切り替える
主キー再作成が失敗する場合
- 重複値・NULLがある → STEP 4-2のチェックを実施
- パーティションテーブルにLOCALでないPKを作ろうとしている
→ パーティションキーがPKカラムに含まれているか確認
- 外部キーが残っている → STEP 4-1のチェックを実施

コメント