That High Watermark Value always eludes me when calculating free space in my tablespaces.

Will the following query consider fragmented free spaces of object below the HWM value for each TS?

select * from sys.sm$ts_free;

(That would be in Oracle 10gR2)

In short, I want to know how much (the total, over and below the HWM) space does Oracle have to keep inserting things.

If my query is wrong, can someone provide one that does that?

pd. I also have other messy queries summing the segment sizes of objects, but then again, I do not know how all this wraps together with fragmented space and HWM. An explanation here would be highly appreciated.

  • answer is long and complex. are u still interested? I can answer you
    – Ste
    May 22, 2013 at 12:03
  • Yes! definitely interested. I was reading quite a bit on it. Since I am studying the subject, can use quality material.
    – filippo
    May 23, 2013 at 19:57
  • Ok, answer is complex, i'll write it tomorrow :) btw the query on ts_free is not totally correct, yes it reports the supposed free space in the tbs but does not let you know where the HWM is.
    – Ste
    May 23, 2013 at 20:08

1 Answer 1


Answer is complex and involves the knowing of different Oracle concepts. I try to explain (sorry if I am going to be too wordy).

The High Water Mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used (Oracle Concept definition). Considering that you are using ASSM (Automatic Segment Space Management)

all blocks above HWM are unformatted and never been used. All blocks below HWM might be:

  • Formatted and contain data

  • Formatted and empty because the data was deleted

  • Allocated, but currently unformatted and unused

DML operations do not release free space from the table below the HWM. Therefore a lot of DML on a table might leave the table fragmented. DDLs always reset the HWM. More consideration about the block use can be done considering two impotant storage parameters: PCTFREE and PCTUSED.

What about HWM in files? The high water mark of a file shows you the smallest size the file could be "shrunk" down to. Any DBA has its own query to find the HWM in a datafile. Personally I use one taken by Tom Kyte and is this:

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = 'db_block_size'

select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)

The HWM position in a datafile does no represent necessarily the free space available in a datafile. You may have a 20Gb tablespace where dba_free_space shows 19gb free but latest HWM is set at 18Gb position.

In conclusion: the position of HWM in a datafile does not represent the real space freely available in the datafile. For this, the query on sm$ts_free is right. Additionally tables can have allocated more space than one is really used.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.