Oracle表碎片整理操作步骤详解

SQL> alter table t1 enable row movement;
表已更改。

SQL> alter table t1 shrink space cascade;
表已更改。

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';

SUM(BYTES)/1024/1024
--------------------
                .125

SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID
';

SUM(BYTES)/1024/1024
--------------------
               .0625

SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  33.5791626

SQL> exec dbms_stats.gather_table_stats('HR','T1',CASCADE=>TRUE);
PL/SQL 过程已成功完成。

这个时候，只剩下0.1M的无用功了，执行计划中，全表扫描也只需要消耗CPU 3
SQL> SELECT TABLE_NAME,
  2         (BLOCKS * 8192 / 1024 / 1024) -
  3         (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
  4    FROM USER_TABLES
  5   WHERE table_name = 'T1';

TABLE_NAME           Data lower than HWM in MB
-------------------- -------------------------
T1                                  .010738373

 
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   396 | 29700 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T1   |   396 | 29700 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

总共只有5个块，空块却有50个，明显empty_blocks信息过期
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         5           50        396

SQL> analyze table t1 compute statistics;
表已分析。

SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';

 
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         5            3        396