A friend called me asking for help because a tablespace did not reduce size after purging the data.
I explained to him how to identify high-water marks, and decided to write this article.
This article explains, with examples, how to view the high-water mark and when the high-water mark is reset. The queries in this article apply when the segment, whose high-water mark must be determined, is in one datafile and is not spawned across multiple data files.
The high-water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high-water mark points to becomes a used block, and the high-water mark is advanced to the next block.
The high-water mark is the level at which blocks have never been formatted to receive data.
When a table is created in a tablespace, some initial number of blocks/extents are allocated to the table. Later, as the number of rows inserted increases, extents are allocated accordingly.
To find out how many blocks/extents are allocated to the table, query DBA_SEGMENTS for ‘blocks’ and ‘extents’.
SQL>create table HWTB (num number) tablespace HW; Table created SQL>select blocks, extents from dba_segments where segment_name='HWTB' BLOCKS EXTENTS -------- ------------- 8 1 Now, to view the high water mark, perform an:
SQL> analyze table test1 compute statistics;
Querying dba_tables for ‘Blocks’ and ‘Empty_blocks’ should give the high-water mark.
Blocks — > Number blocks that has been formatted to recieve data
Empty_blocks —> Among the allocated blocks, the blocks that were never used
SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='HWTB' BLOCKS EMPTY_BLOCKS NUM_ROWS -------------- -------------------------- -------------------- 0 7 0 -- If you insert some rows, then the output of the above query returns: BLOCKS EMPTY_BLOCKS NUM_ROWS -------------- -------------------------- -------------------- 1 6 8 -- blocks+Empty_blocks=1+6=7 (but not 8) because 1 block is for segment header. -- Insert some more rows into table 'HWTB' to increase the number of extents allocated, -- so that DBA_SEGMENTS will show: BLOCKS EXTENTS -------------- --------------- 32 4 -- And dba_tables (after analyze table) shows: BLOCKS EMPTY_BLOCKS NUM_ROWS -------------- -------------------------- -------------------- 24 5 10315
Deleting the records does not lower the high-water mark. Therefore, deleting the records does not raise the ‘Empty_blocks’. After deleting the records, if you query dba_segments or dba_tables, there would be no change. Even an ‘Alter table test1 deallocate unused;’ will not bring the high-water mark down.
To determine the exact number of blocks that contain data i.e. space used by table below the high water mark, query rowid and get the first and last used block from the rowid.
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "used blocks" from HWTB; This works fine if only one file is used for the segment. If more files, we need to include the file number in some way, for instance:
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from HWTB USED_BLOCKS ----------------------- 19
From this, we can conclude that for table ‘HWTB’, 32 blocks are allocated out of which 24 blocks are formatted to receive data but only 19 blocks contain the actual data.
The high-water mark can be reset with a truncate table or if the table is moved to another tablespace.
One option to shrink a segment to reset the high-water mark is the command ALTER TABLE SHIRK SPACE.
SQL> ALTER TABLE HWTB SHRINK SPACE;
When the table is created with CTAS from another table, the high water mark of the latter table is not reflected in the new table. If the table is moved back to the same tablespace, the high water mark is reset. In this case, query on obj#, dataobj# of obj$. Obj# remains the same but dataobj# changes.
Hope this helps you!!!
Disclaimer: “The postings on this site are my own and don’t necessarily represent may actual employer positions, strategies or opinions. The information here was edited to be useful for general purpose, specific data and identifications was removed to allow reach generic audience and to be useful