Enq High water mark contention

High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

When there is a lob column in any table, heavy insert can cause enq high water mark contention due to increase in volume of inserts.

TRUNCATE will reset HWM

The blocks above the HWM level is free blocks, they are ready to use. exception Above the HWM when using the APPEND hint in the INSERT statement (or using DIRECT=Y in sql*loader)


We have to set the event available as below to higher value to reduce enq High water mark contention. ALTER SYSTEM SET EVENT=”44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 - 1024>” scope=spfile;

Situation: Accidentally deleted millions of rows from a table

An Oracle user may accidentally delete millions of rows from a table, then issue a rollback to reverse the changes, a procedure that could take up to a few hours to complete. If there is a requirement to shut down the database without waiting for the rollback to complete, temporarily restart the database with the rollback disabled, perform some DBA tasks, then restart the database with the rollback enabled.


1. alter system set events '10513 trace name context forever, level 2'  scope=spfile;

2. shutdown abort.

3. startup

Then to restart the rollback.

4. alter system set events '10513 trace name context off' scope=spfile;

5. shutdown abort

6. startup