确定表是否存在高水位问题

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