How to determine the high water mark
------------------------------------
To view the high water mark of a particular table::
ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
This will update the table statistics. After generating the statistics,
to determine the high water mark:
SELECT blocks, empty_blocks, num_rows
FROM user_tables
WHERE table_name = <tablename>;
BLOCKS represents the number of blocks 'ever' used by the segment.
EMPTY_BLOCKS represents only the number of blocks above the 'HIGH WATER MARK'
.
Deleting records doesn't lower the high water mark. Therefore, deleting
records doesn't raise the EMPTY_BLOCKS figure.
Let us take the following example based on table BIG_EMP1 which
has 28672 rows (Oracle 8.0.6):
SQL> connect system/manager
Connected.
SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name='BIG_EMP1';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 1024 2
1 row selected.
SQL> connect scott/tiger
SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 28672 700 323
1 row selected.
Note: BLOCKS + EMPTY_BLOCKS (700+323=1023) is one block less than
DBA_SEGMENTS.BLOCKS. This is because one block is reserved for the
segment header. DBA_SEGMENTS.BLOCKS holds the total number of blocks
allocated to the table. USER_TABLES.BLOCKS holds the total number of
blocks allocated for data.
SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
4> FROM big_emp1;
Used
----------
700
1 row selected.
SQL> DELETE from big_emp1;
28672 rows processed.
SQL> commit;
Statement processed.
SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 700 323
1 row selected.
SQL> SELECT COUNT (DISTINCT
2> DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
3> DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
4> FROM big_emp1;
Used
----------
0
1 row selected.
SQL> TRUNCATE TABLE big_emp1;
Statement processed.
SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;
Statement processed.
SQL> SELECT table_name,num_rows,blocks,empty_blocks
2> FROM user_tables
3> WHERE table_name='BIG_EMP1';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
BIG_EMP1 0 0 511
1 row selected.
SQL> connect system/manager
Connected.
SQL> SELECT segment_name,segment_type,blocks
2> FROM dba_segments
3> WHERE segment_name='BIG_EMP1';
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
----------------------------- ----------------- ---------- -------
BIG_EMP1 TABLE 512 1
1 row selected.
Note: TRUNCATE has also deallocated the space from the deleted rows.
To retain the space from the deleted rows allocated to the table use:
TRUNCATE TABLE big_emp1 REUSE STORAGE