表和索引真实使用量分析

1、表的使用分析

参考文章：How to Determine Real Space used by a Table (Below the High Water Mark) (Doc ID 77635.1)

**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 onedatafile 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.

(如果表示存在一个只有一个数据文件的的表空间内，我们只要从ROWID中找出distinct的数量，就得到了数据块使用的个数；

但是，对于一个表存储在多个数据文件里的，就会有相同的block numner，但是数据文件号是不同的，所以这个时候可以distinct ROWID  block_number+file)

SQL> create table tab_test4 as select * from dba_objects;
Table created.

SQL> select count(*) from tab_test4;
  COUNT(*)
----------
     74491

SQL> ANALYZE TABLE TAB_TEST4 ESTIMATE STATISTICS;  (这样统计信息就可以存入usr_tables表中了)
Table analyzed.

通过如下命令，查看使用block的数量：

  SELECT COUNT (DISTINCT 
         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||
         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"
    FROM user_test1.tab_test4;

      Used
------------
      1010

也就是说，有1010个数据块被使用。

执行truncate表：

SQL> TRUNCATE TABLE tab_test4;

Table truncated.

SQL>  ANALYZE TABLE TAB_TEST4 ESTIMATE STATISTICS;

Table analyzed.

再次查看：

    SELECT  table_name, num_rows,blocks,empty_blocks
  FROM dba_tables
  WHERE table_name='TAB_TEST4';

2、索引使用分析

参考文章：How to Find Out How Much Space an Index is Using (Doc ID 33343.1)