表中真正使用的空间(How to Determine Real Space used by a Table (Below the High Water Mark)

How to Determine Real Space used by a Table (Below the High Water Mark) [ID 77635.1]
      修改时间 01-NOV-2011     类型 BULLETIN     状态 PUBLISHED     


***Checked for relevance on 31-Oct-2011***

PURPOSE
This article describes how to find out how many blocks are really being

used within a table ie. are not empty. Please note that this article does

not cover what to do when chaining is taking place.

SCOPE & APPLICATION
For DBA's needing to determine how many blocks within a table are
empty blocks.

How many blocks contain data (are not empty)
--------------------------------------------
Each row in the table has pseudocolumn called ROWID.
This pseudo contains information about physical location
of the row in format
                     block_number.row.file

If the table is stored in a tablespace which has one
datafile, all we have to do is to get DISTINCT
number of block_number from ROWID column of this table.

But if the table is stored in a tablespace with more than one
datafile then you can have the same block_number but in
different datafiles so we have to get DISTINCT number of
block_number+file from ROWID.

The SELECT statements which give us the number of "really used"
blocks is below. They are different for ORACLE 7 and ORACLE 8
because of different structure of ROWID column in these versions.

For ORACLE 7:

   SELECT COUNT(DISTINCT SUBSTR(rowid,15,4)||
                         SUBSTR(rowid,1,8)) "Used"
     FROM schema.table;


For ORACLE 8+:

  SELECT COUNT (DISTINCT
         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
    FROM schema.table;
or

    SELECT COUNT (DISTINCT SUBSTR(rowid,1,15)) "Used"
    FROM schema.table;

You could ask why the above information could not be determined
by using the ANALYZE TABLE command. The ANALYZE TABLE command only
identifies the number of 'ever' used blocks or the high water mark
for the table.

What is the High Water Mark?
----------------------------
All Oracle segments have an upper boundary containing the data within
the segment. This upper boundary is called the "high water mark" or HWM.
The high water mark is an indicator that marks blocks that are allocated
to a segment, but are not used yet. This high water mark typically bumps
up at 5 data blocks at a time. It is reset to "zero" (position to the start
of the segment) when a TRUNCATE command is issued.  So you can have empty
blocks below the high water mark, but that means that the block has been
used (and is probably empty caused by deletes). Oracle does not move the
HWM, nor does it *shrink* tables, as a result of deletes.  This is also
true of Oracle8.  Full table scans typically read up to the high water mark.
 
Data files do not have a high water mark; only segments do have them.
 
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

References
----------

Note: 33343.1 How to Find Out How Much Space an Index is Using

实验：

SQL> desc test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------

 ID                                                 NUMBER

SQL> drop table test purge;

表已删除。

SQL> create table test(id number);

表已创建。

SQL> insert into test values(1);

已创建 1 行。

SQL> insert into test values(2);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select segment_name,segment_type,blocks,extents
  2  from dba_segments
  3  where segment_name = upper('test');

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS    EXTENTS
------------------------------ ------------------ ---------- ----------
TEST                           TABLE                       8          1

SQL> analyze table test compute statistics;

表已分析。

SQL> select table_name,num_rows,blocks,empty_blocks
  2  from user_tables
  3  where table_name = 'TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
TEST                                    2          5            3

BLOCKS代表segment中已经使用的块数，EMPTY_BLOCKS代表HWM上面的块数。

SQL> SELECT COUNT (DISTINCT
  2    DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
  3    DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
  4  FROM test;

      Used
----------
         1
SQL> delete from test;

已删除2行。

SQL> commit;

提交完成。

SQL> analyze table test estimate statistics;

表已分析。

SQL> select table_name,num_rows,blocks,empty_blocks
  2  from user_tables
  3  where table_name = 'TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
TEST                                    0          5            3

SQL> SELECT COUNT (DISTINCT
  2  DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
  3  DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
  4  from test;

      Used
----------
         0

SQL> truncate table test;

表被截断。

SQL> analyze table test estimate statistics;

表已分析。

SQL> select table_name,num_rows,blocks,empty_blocks
  2  from user_tables
  3  where table_name = 'TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
TEST                                    0          0            8

SQL> conn /as sysdba
已连接。
SQL> select segment_name,segment_type,blocks
  2  from dba_segments
  3  where segment_name = 'TEST';

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
------------------------------ ------------------ ----------
TEST                           TABLE                       8

SQL> select segment_name,segment_type,blocks,extents
  2  from dba_segments
  3  where segment_name = upper('test');

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS    EXTENTS
------------------------------ ------------------ ---------- ----------
TEST                           TABLE                       8          1