High Water Mark.

[shahnazurs]

could you please tell me what is high water mark in oracle terminology? Please explain with example.

[alit2002]

Hi

You have to understand Oracle storage.

Each table is made up of extents and each extent is made up of oracle blocks - a common block size is 8k. So you have a table with 10 extents (80K).

You populate your table with 2 million rows of data and you will have many hundreds of extents. Now lets assume that you delete over half of the records in the table. Oracle still has the same number of extents but many of the blocks are empty. When you run a query against the table Oracle will scan through all the blocks including empty ones looking for data. So you can think of the total number of extents / blocks used as the high water mark.

To fix you export the table, drop it and import it back in.

Alistair

[sumon2b]

after deleting rows high water mark remain same, what will happen if i insert new rows now.where this new rows will be stored? above the HWM or below?

[alit2002]

Yes, after deleting rows the high water mark is the same. New rows will use the space left by those that were deleted unless they don't fit into that space then new space is allocated and the high water mark increases. To reduce the highwater mark export / datapump the table, drop it and import it back in again.

[Koolhemu]

If you have to remove the whole data,then in that case Truncate command will be better then Delete because it will release the memory and the High water mark will be resetted to the original value.Hence later on, fetching on that table will be faster.

[vinod dussa]

*Emphasized Text Here

Each table is made up of extents and each extent is made up of oracle blocks - a common block size is 8k.

*

[vinod dussa]

HIGH WATER MARK

NOTE:- Each table is made up of extents and each extent is made up of oracle blocks - a common block size is 8k.

TABLE -> EXTENTS -> BLOCKS -> 8K

EXAMPLE:- Suppose if there are 100rows in a table ,then the high water mark in a table is present at the

100th row ,and later on you deleted the 50rows now the table contains 50rows,but the high water mark

is still at 100th row, so if u add more rows in a table the the high water mark increases .

SOLUTION:- To reduce the high water mark in a table use export-datapump utility and

then import it using import-datapump utility.

2nd option :- (after export use truncate to delete the table).

EFFECTS :- The high water mark decreases.

Same in case of data if u want to remove whole data then use Truncate command,

will be better then Delete because it will release the memory and the High water mark will be

resetted to the original value.Hence later on, fetching on that table will be faster....

THANKS & BEST REGARDS

VINOD DUSSA