High water mark for a table
Where can one find the high water mark for a table? (for DBA)
Â
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 SEGMENT_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.