oracle+怎么清理碎片,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

这就是微学网-程序员之家为你提供的"Oracle表碎片整理操作步骤详解"希望对你有所帮助.本文来自网络，转载请注明出处：http://www.weixuecn.cn/article/6389.html