Oracleのテーブル表領域を移動する際のポイント

2022年5月8日
ＩＴ関連
ORACLE

ＩＴ関連

システムエンジニアの皆さんがよく遭遇するであろう、
Oracleの領域の枯渇。

表領域Aがもうすぐ１００％になる！？
表領域Bはまだ、３０％なのに。

どーしよー？

そんな困った！！の解決方法を実運用に即した内容でご紹介します。

1 Oracleの表領域の使用率を確認するには
2 表領域の変更とインデックスの再構成
3 テーブルの統計情報を取得する
4 まとめ

Oracleの表領域の使用率を確認するには

まず、Oracleの表領域の使用率を確認しておく必要があります。
Object Browserなどのツールを利用されている方は、
個別にコマンドを実行しなくても確認ができます。
例）【メニュー】→【管理】→【表領域情報】

その他のツールでも、わりと用意されていたりはするので、
捜してみましょう。

さて、それでは、ツールがない方向けの参考コマンドですが、
OracleのDBAテーブルに以下２つのテーブルがあります。

DBA_DATA_FILES

DBA_DATA_FILES・・・データベースファイルを示します。

【代表的な項目】

FILE_ID	データベース・ファイルのファイル識別子番号
TABLESPACE_NAME	ファイルが属する表領域の名前
BYTES	バイト単位のファイル・サイズ

このテーブルから、表領域ごとの全体サイズを確認することができます。
例えば、以下のSQLを実行します。

select
tablespace_name,
sum(bytes) total_bytes　　– ①全体サイズ
from
dba_data_files
group by
tablespace_name

DBA_FREE_SPACE

DBA_FREE_SPACE・・・データベース内のすべての表領域の使用可能エクステントを示します。

【代表的な項目】

TABLESPACE_NAME	エクステントが設定されている表領域の名前
BYTES	エクステントのサイズ(バイト)

このテーブルから、表領域ごとの空きサイズを確認することができます。
例えば、以下のSQLを実行します。

select
tablespace_name free_tablespace_name,
sum(bytes) free_total_bytes –②空きサイズ
from
dba_free_space
group by
tablespace_name

表領域のサイズを確認するSQL

紹介した以上２つのテーブルを”tablespace_name ”で結合することで、
テーブル領域ごとの全体サイズ、使用サイズ、空きサイズ、使用率を抽出することが可能です。

全体サイズは、DBA_DATA_FILESのBYTESの合計、
空きサイズは、DBA_FREE_SPACEのBYTESの合計、
使用サイズは、DBA_DATA_FILESのBYTESの合計　ー　DBA_FREE_SPACEのBYTESの合計、
使用率は、使用サイズ／全体サイズ
で調べるSQLが作成できます。
例えば、以下のようなSQLを実行します。（※一部、上述したものを置き換えてください）
ちょっとJOIN句を古臭い書き方してますが、普通にINNER JOINでも可能です。

select
tablespace_name,
to_char(nvl(total_bytes / 1024 / 1024,0),’999,999,999′) AS “SIZE(MB)”,
to_char(nvl((total_bytes – free_total_bytes) / 1024 / 1024,0),’999,999,999′) AS “USED(MB)”,
to_char(nvl(free_total_bytes / 1024 / 1024,0),’999,999,999′) AS “FREE(MB)”,
round(nvl((total_bytes – free_total_bytes) / total_bytes * 100,100),2) AS “RATE(%)”
from
(【DBA_DATA_FILES】のSQL),　–　上記参照
(【DBA_FREE_SPACE】のSQL)　–　上記参照
where
tablespace_name = free_tablespace_name(+)
order by
tablespace_name;

表領域の変更とインデックスの再構成

表領域の使用率を確認したら、使用率が高い表領域から使用率の低い表領域へ
使用率の高い表領域を利用しているテーブルを移動していきます。

テーブルの表領域を確認する

USER_TABLESを確認することで、テーブルの表領域を確認することができます。
※パーティション表の場合は、USER_TAB_PARTITIONSとなります。

【代表的な項目】

OWNER	表の所有者
TABLE_NAME	表の名前
TABLESPACE_NAME	表が設定されている表領域の名前。パーティション表、一時表および索引構成表の場合は、NULL

こちらのテーブルから、テーブルの表領域を確認することができるため、
空き容量の少ない表領域に属するテーブルを確認します。
例えば以下のようなSQLを実行します。

select
table_name,
tablespace_name
from
user_tables
where
tablespace_name = ‘XXXX’
order by
tablespace_name;

テーブルの表領域を移動する

空き容量の少ない表領域に属するテーブルを確認したら、
空き容量の多い表領域へ移動していきます。
以下のようなSQLを実行します。

alter table [空き容量の少ない表領域に属するテーブル]
move tavlespace [空き容量の多い表領域];　– []は不要

※パーティションテーブルの場合は、パーティションの指定も必要です。

テーブルの表領域を移動してしまうと、Oracleが内部的に保持している値が別の値となるため、
移動したテーブルの索引を再構成する必要があります。

インデックスを再構成する

テーブルの表領域を移動した場合、テーブルの索引を再構成する必要がありますので、
移動したテーブルが保持しているインデックスを再構成します。
以下のようなSQLを保持しているインデックス分、実行します。

alter index [移動したテーブルが保持するインデックス] rebuild;　– []は不要

テーブルの統計情報を取得する

テーブルの表領域の移動およびインデックスの再構成をしていますが、
テーブルの統計情報を取得することをオススメします。
統計情報の取得については、以下のようなSQLを実行します。

analyze table [テーブル名] compute statistics;　– テーブル全体に対して、統計情報を取得する場合
analyze table [テーブル名] estimate statistics sample 10 percent;　– テーブルの１０％をサンプリングして、統計情報を取得する場合