Oracle Determine HWM and reduce it by shrink space

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. But when you delete the Rows then HWM cannot come down automatically, extra steps needs to done to bring down HWM.

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.

SELECT e.file_id, tablespace_name, file_name, bytes/1024/1024,user_bytes/1024/1024, hwm, CEIL((NVL(e.hwm, 1) * 8192)/1024/1024) as “MB”
dba_data_files f
LEFT JOIN (SELECT file_id, max(block_id + blocks – 1) hwm FROM dba_extents GROUP BY file_id) e ON f.file_id = e.file_id
WHERE f.file_name = ‘/your/path/your_file01.dbf’


Table altered.


Table altered.

SQL> analyze table TBS_DATA compute statistics ;

Table analyzed.

SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name =’TBS_DATA’;

———- ———— ———-

SQL> SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) – (NUM_ROWS*AVG_ROW_LEN/1024/1024) “Data lower than HWM in MB” FROM USER_TABLES WHERE table_name=’TBS_DATA’;
TABLE_NAME Data lower than HWM in MB

To reduce the high water mark:
a. Take export of table and re-import back
b. Truncate the table (only if you want to remove data/rows completely)

Suppose we create an empty table, the high-water mark would be at the beginning of the table segment

X Unused space


When you insert rows into the table the high watermark will be bumped up step by step. This is done by the server process which makes the inserts.

Used DATA/Rows X Un used Space


Now if we insert a table with 10,000 rows. And let’s assume that we deleted 5,000 rows later.

Used data Empty blocks X Un used space


Full table scan

As you seen above by deleting the data, HWM does not move.  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.

NOTE:  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.

So, now how we set the high-water mark at lower position?

The only way to set the HWM is to truncate a table. Let us see how truncate set the HWM.

No data in the segment


HWM is reset now, after truncating data.

HWMBLOCKS = “Blocks Containing data” + “Empty blocks”

HWMBLOCKS – “Blocks containing data”

HWM % = (———————————-)  * 100


You can also use DBMS_SPACE.UNUSED_SPACE procedure to determine HWM.

High Watermark = Total blocks – Unused blocks – 1

SQL> select blocks from dba_segments where owner=upper(‘HR’) and segment_name = upper(‘JOBS’);

SQL> analyze table estimate statistics;

SQL> select empty_blocks from dba_tables where owner=upper(‘HR’) and table_name = upper(‘jobs’);

Ways for Resetting HWM………..

Option 1
Alter table  tab_name  move tablespace

This option requires rebuilding of indexes. Since the index will be invalid/unusable after running the above command. Also users cannot use the application or reports while rebuilding the index. Make sure you stop Goldengate if this table is in extract process otherwise it will go ABEND (because of Unusable indexes)

Option 2

  1. Export the data
    2. Truncate the table
    3. Import the table
    4. Analyze the table

Option 3
  1. Copy the table data
2. Truncate the original table
3. Insert back.

    Option 4
    Use DBMS_REDEFINITION package to copy the table (redefining the Table structure)

People confused about setting of HWM through ALTER TABLE DEALLOCATE UNUSED clause. This clause only frees unused space above the high water mark but cannot reset HWM position.

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 table_name;
Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark,
it is called segment shrinking and is only possible for segments which use ASSM, in other words, which are located in tablespaces which use Automatic Segement Space Management.

How to View High Water Mark – Step-by-Step Instructions (Doc ID 262353.1)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s