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)
