DB縮小リサイズができない

レコードを削除して表領域を空けたのに、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のチェックを実施

コメント

タイトルとURLをコピーしました