Dev's Weblog

Reading time: < 1 minute

There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:

SELECT BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER=UPPER(owner) AND SEGMENT_NAME = UPPER(table);

ANALYZE TABLE owner.table ESTIMATE STATISTICS;

SELECT EMPTY_BLOCKS
FROM DBA_TABLES
WHERE OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table);

Thus, the tables' HWM = (query result 1) - (query result 2) - 1

NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.
and if the index rebuild...

http://www.quest-pipelines.com/newsletter-v2/rebuild.htm

Reference:

http://forums.oracle.com/forums/thread.jspa?messageID=2655499&tstart=0