Oracleデータファイルの現在サイズと縮小可能な最小サイズを調べる方法は？縮小するSQLも生成！

Database
Oracleデータファイルの現在サイズと縮小可能な最小サイズを調べる方法は？

Oracleデータベースは日頃からデータベースのメンテナンスをしていないと、どんどんデータベースファイルいっぱいになっていき、容量不足を解消しようとしてデータファイルを追加して容量を増やしていくと、ディスク容量が足りなくなってきてしまいます。そのような場合に使えるSQLを紹介します。このSQLを使うことによって、無駄なデータファイルを縮小することが出来ます。Oracleデータベース10g以上で実際に検証しました。

私が管理するデータベースが肥大化してしまった時に色々と探してみたのですが、なかなかこのような方法が紹介されていませんでしたので共有したいと思います。これを使って肥大化してしまったデータベースの状況を分析してデータベースサイズを縮小してみましょう。

現在のデータファイルのサイズ・縮小可能なサイズを取得するSQL

-- 1行の文字数、各列値のフォーマット設定
SET LINESIZE 200
COLUMN file_name FORMAT a50
COLUMN value FORMAT a20
COLUMN current_size FORMAT 999,990 HEADING "current_size (mb)"
COLUMN est_smallest_size FORMAT 999,990 HEADING "est_smallest_size (mb)"
COLUMN est_saving_size FORMAT 999,990 HEADING "est_saving_size (mb)"
COLUMN cmd FORMAT a100

-- 変数の置換前後の値を表示しない
SET VERIFY OFF

-- データベースのブロックサイズを取得しblksize変数に格納
COLUMN value new_val blksize
SELECT value FROM v$parameter WHERE name = 'db_block_size';


-- データファイル毎の現在のデータファイルサイズ、最小化可能なサイズ、節約可能なサイズを取得
SELECT
  file_name,
  CEIL(blocks * &&blksize / 1024 / 1024) AS current_size,
  CEIL((NVL(hwm, 1) * &&blksize) / 1024 / 1024) AS est_smallest_size,
  CEIL(blocks * &&blksize / 1024 / 1024) - CEIL((NVL(hwm, 1) * &&blksize) / 1024 / 1024) AS est_saving_size
FROM dba_data_files a
LEFT OUTER JOIN
  (SELECT
    file_id,
    MAX(block_id + blocks - 1) hwm
  FROM dba_extents
  GROUP BY file_id) b ON (a.file_id = b.file_id);

この一連のSQLをSQL*PlusやSQLclで実行すると、次のような結果が得られます。

VALUE
--------------------
8192

FILE_NAME                         current_size (mb) est_smallest_size (mb) est_saving_size (mb)
--------------------------------- ----------------- ---------------------- --------------------
/opt/oradata/test/system01.dbf               570                    551                   19
/opt/oradata/test/dbfile17.dbf             8,192                  8,192                    0
/opt/oradata/test/dbfile19.dbf             8,000                  8,000                    0
/opt/oradata/test/dbfile09.dbf             6,144                  6,140                    4
/opt/oradata/test/dbfile22.dbf             4,200                  4,123                   77
/opt/oradata/test/dbfile02.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile07.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile10.dbf             6,144                  6,144                    0
/opt/oradata/test/dbfile15.dbf             8,192                  8,192                    0
/opt/oradata/test/dbfile16.dbf             8,192                  8,192                    0
/opt/oradata/test/undotbs01.dbf           32,768                    460               32,308
/opt/oradata/test/undotbs02.dbf           17,376                    172               17,204
/opt/oradata/test/dbfile01.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile20.dbf             8,000                  8,000                    0
/opt/oradata/test/users01.dbf                  5                      1                    4
/opt/oradata/test/dbfile04.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile18.dbf             8,192                  8,192                    0
/opt/oradata/test/sysaux01.dbf             2,740                  2,690                   50
/opt/oradata/test/dbfile13.dbf             8,000                  8,000                    0
/opt/oradata/test/dbfile21.dbf             8,192                  3,969                4,223
/opt/oradata/test/dbfile03.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile11.dbf             6,144                  6,144                    0
/opt/oradata/test/dbfile14.dbf             8,000                  8,000                    0
/opt/oradata/test/dbfile05.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile12.dbf             6,144                  6,144                    0
/opt/oradata/test/dbfile06.dbf            10,240                 10,240                    0
/opt/oradata/test/dbfile08.dbf             6,144                  6,144                    0

27 rows selected.

それぞれの列値の意味は次の通りです。

FILE_NAMEデータファイル名
current_size (mb)算出された現在のデータファイルサイズ(単位はメガバイト)
est_smallest_size (mb)最も小さくすることが出来るデータファイルのサイズ(単位はメガバイト)の見積値
est_saving_size (mb)節約可能なデータファイルのサイズ(単位はメガバイト)の見積値

これにより、それぞれのデータファイルがどの程度のサイズなのか、そして縮小可能な最小値はいくつなのか、節約可能なサイズはどの程度なのかを調べることが出来ます。

実際に上記の例では、「system01.dbf」というデータファイルの現在のサイズが570MB、最も小さく出来るサイズの見積もりは551MB、つまり節約可能なサイズは19MBである、ということがわかります。

データファイルのサイズを縮小するためのSQLを生成

さらに、以下のSQLを実行することによって、実際にデータファイルを小さくすることが可能なデータファイルに対する、容量を縮小するためのSQL文を出力します。

-- 各データファイルのサイズを縮小するSQLを出力
SELECT
  'ALTER DATABASE DATAFILE ''' || file_name || ''' resize ' || CEIL((NVL(hwm, 1) * &&blksize) / 1024 / 1024) || 'M;' AS cmd
FROM dba_data_files a
LEFT OUTER JOIN (SELECT file_id, MAX(block_id + blocks - 1) hwm
  FROM dba_extents
  GROUP BY file_id) b ON (a.file_id = b.file_id)
WHERE CEIL(blocks * &&blksize / 1024 / 1024) - CEIL((NVL(hwm, 1) * &&blksize) / 1024 / 1024) > 0;

結果として以下のようなSQL分が出力されますので、このSQLをコピー＆ペーストして各データファイルのサイズを縮小します。

CMD
------------------------------------------------------------------------
ALTER DATABASE DATAFILE '/opt/oradata/test/system01.dbf' resize 551M;
ALTER DATABASE DATAFILE '/opt/oradata/test/dbfile09.dbf' resize 6140M;
ALTER DATABASE DATAFILE '/opt/oradata/test/dbfile22.dbf' resize 4123M;
ALTER DATABASE DATAFILE '/opt/oradata/test/undotbs01.dbf' resize 460M;
ALTER DATABASE DATAFILE '/opt/oradata/test/undotbs02.dbf' resize 172M;
ALTER DATABASE DATAFILE '/opt/oradata/test/users01.dbf' resize 1M;
ALTER DATABASE DATAFILE '/opt/oradata/test/sysaux01.dbf' resize 2690M;
ALTER DATABASE DATAFILE '/opt/oradata/test/dbfile21.dbf' resize 3969M;

8 rows selected.

まとめ

以上のように各データファイル毎の現在のサイズ、縮小可能な最小データファイルサイズ、縮小によって節約可能なサイズを一度に調べることが出来ます。また縮小可能な各データファイルのサイズ変更を行うためのSQLも出力するSQLを用意しました。このSQLを利用してデータベース管理が少しでも楽になればと思います。

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