All the Oracle Segments will have upper boundary congaing the data with in the segment.
the upper boundary is called HighWaterMark-HWM
The HWM is an indication that marks all blocks are allocated but not used yet.
You can also use DBMS_SPACE.UNUSED_SPACE procedure to determine HWM.
To view the high water mark of a particular table::
ANALYZE TABLE OWNER.TABNAME ESTIMATE/COMPUTE STATISTICS;
This will update the table statistics.
After generating the statistics,
to determine the high water mark:
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='tabname' ;
BLOCKS represents the number of blocks ‘ever’ used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the ‘HIGH WATER MARK’.
Deleting records doesn’t lower the high water mark. Therefore, deleting
records doesn’t raise the EMPTY_BLOCKS figure.
"Only Truncating resets the High Water Mark."
Ways to reset HWM :-
the upper boundary is called HighWaterMark-HWM
The HWM is an indication that marks all blocks are allocated but not used yet.
Oracle uses the high water mark to identify the highest amount of
space used by a particular segment. It acts as the boundary between used
and unused space. As the amount of data grows due to row inserts and
updates, the segment's high water mark grows accordingly.
The
HIGH WATER MARK is set at the beginning of the segment when the table
is created. Deleting rows will never reset the HWM even if all the rows
are deleted from the table. Only the TRUNCATE command will reset the
HWM.
The
main disadvantage of this is that oracle always read the blocks up to
high water mark in case of full table scan. You may have ever notice
that doing a count (*) on empty table, takes time to show you 0 rows.
The reason for delay is setting of HWM at higher position.
"Whenever
optimizer takes full table scan, it scans all the blocks below HWM.
This would degrade the performance and slowdown the Full table scan
process. To avoid this, we need to shrink the table to reset the HWM."
HWMBLOCKS = “Blocks Containing data” + “Empty blocks”
HWMBLOCKS – “Blocks containing data”
HWM % = (----------------------------------) * 100
HWMBLOCKS
|
You can also use DBMS_SPACE.UNUSED_SPACE procedure to determine HWM.
High Watermark = Total blocks - Unused blocks – 1
ANALYZE TABLE OWNER.TABNAME ESTIMATE/COMPUTE STATISTICS;
This will update the table statistics.
After generating the statistics,
to determine the high water mark:
SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='tabname' ;
BLOCKS represents the number of blocks ‘ever’ used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the ‘HIGH WATER MARK’.
Deleting records doesn’t lower the high water mark. Therefore, deleting
records doesn’t raise the EMPTY_BLOCKS figure.
"Only Truncating resets the High Water Mark."
Ways to reset HWM :-
Method-1
Alter table tab_name move tablespace
Alter table tab_name move tablespace
This option requires rebuilding the indexes. Since the index will be
invalid/unusable after running the above command.it'll be a overhead to the DBA to rebuild them
Method-2
1. Export the data
2. Truncate the table
3. Import the table
4. Analyze the table
2. Truncate the table
3. Import the table
4. Analyze the table
