实例讲解表碎片的具体起因及解决的办法

ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
b) SELECT blocks, empty_blocks, num_rows 
FROM user_tables 
WHERE table_name = <tablename>;

1) SQL> SELECT segment_name,segment_type,blocks 
FROM dba_segments 
WHERE segment_name='BIG_EMP1'; 
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS 
----------------------------- ----------------- ---------- ------- 
BIG_EMP1 TABLE 1024 2 
1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; 
Statement processed. 

3) SQL> SELECT table_name,num_rows,blocks,empty_blocks 
FROM user_tables 
WHERE table_name='BIG_EMP1'; 
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS 
------------------------------ ---------- ---------- ------------ 
BIG_EMP1 28672 700 323 
1 row selected. 

注意：
BLOCKS   EMPTY_BLOCKS (700 323=1023)比DBA_SEGMENTS.BLOCKS少个数据库块，
这是因为有一个数据库块被保留用作segment header。DBA_SEGMENTS.BLOCKS 
表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使
用过的数据库块的数目。

4) SQL> SELECT COUNT (DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| 
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used" 
FROM big_emp1; 
Used 
---------- 
700 
1 row selected. 

5) SQL> DELETE from big_emp1; 
28672 rows processed. 

6) SQL> commit; 
Statement processed. 

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; 
Statement processed. 

8) SQL> SELECT table_name,num_rows,blocks,empty_blocks 
FROM user_tables 
WHERE table_name='BIG_EMP1'; 
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS 
------------------------------ ---------- ---------- ------------ 
BIG_EMP1 0 700 323 
1 row selected. 

9) SQL> SELECT COUNT (DISTINCT 
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)|| 
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used" 
FROM big_emp1; 
Used 
---------- 
0 -- 这表名没有任何数据库块容纳数据，即表中无数据
1 row selected. 

10) SQL> TRUNCATE TABLE big_emp1; 
Statement processed. 

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS; 
Statement processed. 

12) 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. 

13) SQL> SELECT segment_name,segment_type,blocks 
FROM dba_segments 
WHERE segment_name='BIG_EMP1'; 
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS 
----------------------------- ----------------- ---------- ------- 
BIG_EMP1 TABLE 512 1 
1 row selected.