bhanushiva

 Profile Answers by bhanushiva 

• Jan 19th, 2006

 

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.

Regards

Bhanu Shivakumar

kumaresan.m

 Profile 

• Sep 12th, 2006

 

high water mark is concerned with segments not with the datafiles

after analysing the segments, this query will give the hwm for the segment

select (blocks-empty_blocks) HWM from dba_segments where segment_name=upper('name') and segment_owner=upper('name');