データファイルのハイウォーター・マーク(最高水位標/High-water Mark/HWM)を確認する

データファイル縮小時などに、ハイウォーター・マーク(以降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です。

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