データファイルのハイウォーター・マーク(最高水位標/High-water Mark/HWM)を確認する
対応バージョン
10.2 - 19.1
2019-11-27記事公開
データファイル縮小時などに、ハイウォーター・マーク(以降HWM)の確認を行いたい場合に、縮小可能なデータファイルのサイズを取得するためのSQLです。SHRINK等と組み合わせて利用してください。
マニュアルに記載されていない内容ですので、本記事の内容を利用する場合には、自己責任にて実施をお願いいたします。
HWMについて
HWMとは、セグメント内の使用済領域と未使用領域の境界を指します。
データファイルの縮小可能な領域を見る場合は、データファイル内にあるセグメント（表やインデックスなどの単位）の使用済みブロックの最大値を確認します。
HWMは、各セグメントにおける断片化の把握などにも利用することがあります。
参考資料
Oracle Database 19c データベース概要
用語集
https://docs.oracle.com/cd/F19136_01/cncpt/glossary.html#GUID-EFC35457-CEA1-4104-8E24-765B4F9FA615
HWMの確認方法
以下のSQLにてデータファイルのHWMが示す位置のサイズを確認できます。CURRENT_SIZEが現在のファイルサイズを示しており、SMALLESTのサイズまで縮小できます。SAVINGSに示すサイズの縮小が可能です。 ※ 単位はMBytesです。|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> column FILE_NAME format a50 SQL> set pages 1000 line 120 SQL> SQL> select file_name 2 , ceil( (nvl(dehwm.HWM,1)*bsz.BLOCK_SIZE)/1024/1024 ) SMALLEST 3 , ceil( ddf.BLOCKS*bsz.BLOCK_SIZE/1024/1024 ) CURRENT_SIZE 4 , ceil( ddf.BLOCKS*bsz.BLOCK_SIZE/1024/1024 ) - ceil( (nvl(dehwm.HWM,1)*bsz.BLOCK_SIZE)/1024/1024 ) SAVINGS 5 from DBA_DATA_FILES ddf 6 , (select FILE_ID 7 , max(BLOCK_ID + BLOCKS - 1) HWM 8 from DBA_EXTENTS 9 group by FILE_ID ) dehwm 10 , (select to_number(VALUE) block_size from V$PARAMETER where NAME = 'db_block_size') bsz 11 where ddf.FILE_ID = dehwm.FILE_ID(+) 12 order by SAVINGS desc; FILE_NAME SMALLEST CURRENT_SIZE SAVINGS -------------------------------------------------- ---------- ------------ ---------- /opt/oracle/oradata/XE/sysaux01.dbf 983 1030 47 /opt/oracle/oradata/XE/undotbs01.dbf 33 65 32 /opt/oracle/oradata/XE/system01.dbf 845 850 5 /opt/oracle/oradata/XE/users01.dbf 1 5 4 |
参考資料
https://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=52401
