*******************************************************************
* Description: Knowledge of Oracle data file block high water mark
* Compatiablity: RDBMS 11g, 12c
* Date: 10:19 PM EST, 04/13/2017
*******************************************************************


<1> High Water Mark (HWM) - boundary between used and unused space:
     |
     |__ High Water Mark = TOTAL BLOCKS - UNUSED BLOCKS - 1 = USER_SEGMENTS.BLOCKS - USER_TABLES.EMPTY_BLOCKS - 1
     |
     |__ SQL> SELECT file_id, MAX(block_id+blocks-1) hwm FROM dba_extents GROUP BY file_id;
     |
     |                   FILE_ID        HWM
     |                ---------- ----------
     |                         1     111375
     |                         6    2234559
     |                         2    1560319
     |                         4     240511
     |                         5    4027228
     |                         3      18047
     |                         7       2943
     |                         9        143
     |
     |
     |
     |__ 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. The high water mark divide a segment into used blocks free blocks.
     |   Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. 
     |   Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan. 
     |  
     |__ Oracle keeps track of the high water mark for a segment in the segment header. Moving the high water mark In normal DB operations, the high water mark only moves upwards,
         not downwards. The exceptions being the truncate. If there is a lot of free space below the high water mark, one might consider to use alter table move statements.