Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

This question came from our site for professional and enthusiast programmers.

answer is long and complex. are u still interested? I can answer you
–
SteMay 22 '13 at 12:03

Yes! definitely interested. I was reading quite a bit on it. Since I am studying the subject, can use quality material.
–
filippoMay 23 '13 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.
–
SteMay 23 '13 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:

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.