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

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