Home  >  Oracle Database Administration (DBA)  >  Scripts  >  Find the "file" High Water Mark (HWM)

NOTE:

HWM is generally used with segments. In fact, the blocks below the high water mark (= is a block/ block number) were used at one time.

SELECT a.tablespace_name,

               file_name,

               c.VALUE / 1024 "Blk. size(Kb)",
               CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
               CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",
               CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"
FROM dba_data_files a,
            (SELECT file_id, MAX (block_id + blocks - 1) hwm
                FROM dba_extents
             GROUP BY file_id) b,
            (SELECT VALUE
                FROM v$parameter
              WHERE NAME = 'db_block_size') c
WHERE a.file_id = b.file_id(+);