ALTER TABLE MOVE和SHRINK SPACE区别

Alter table move或者shrink space可以收缩段，消除部分行迁移、消除空间碎片、使数据更紧密，但move跟shrink space还是有区别的。

       Move会移动高水位，但不会释放申请的空间，是在HWM以下的操作。

       Shrink space同样会移动hwm，但也会释放申请的空间，是在hwm上下都有的操作。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

PL/SQL Release 10.2.0.4.0 - Production

CORE   10.2.0.4.0     Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production 

首先用alter table move方式：

创建测试表

SQL> create table test(id number) storage(initial 10m next 1m) tablespace scott_test;
Table created.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables
  2  where table_name='TEST';
TABLE_NAME    BLOCKS   EMPTY_BLOCKS
--------------------- ------------------ -------------------------
TEST               0        1280

SQL> select segment_name,extents,blocks from user_segments
2  where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS

--------------------- ------------------- ---------------
TEST              10         1280

SQL> begin 
 2    for i in 1..100000 loop
 3    insert into test values(i);
 4    end loop;
 5 end;
 6 /

SQL> analyze table test compute statistics; -- 对 test 表操作后，只有对该表做过分析后（ compute statistics ），数据字典才会记录该表修改后的状态
Table analyzed.

SQL> col segment_name format a20

SQL> select table_name,blocks,empty_blocks from user_tables 
 2  where table_name='TEST';
TABLE_NAME    BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST             186        1094   -- 空闲块降低了

SQL> select segment_name,extents,blocks from user_segments
 2 where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS
---------- ---------- ----------
TEST              10      1280

插入10万行后，分配空间没变，分配的10个extents并未使用完，使用了186个blocks,空闲1094个blocks，此时hwm为186。

SQL> delete from test where rownum <=50000;  --删除一半的数据
50000 rows deleted.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables
  2  where table_name='TEST';
TABLE_NAME    BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST             186        1094

SQL> select segment_name,extents,blocks from user_segments
 2 where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS
---------- ---------- ----------
TEST              10      1280

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks
2  from test;

USED_BLOCKS
-----------
        77

删除一半数据后仍然使用186个blocks，而真正使用了77个blocks。Hwm不变。

SQL> alter table test move;
Table altered.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables
 2   where table_name='TEST';
TABLE_NAME    BLOCKS  EMPTY_BLOCKS
---------- ---------- ------------
TEST              81        1199    --move后，空闲块增加了，hwm降低了

SQL> select segment_name,extents,blocks from user_segments
 2 where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS
---------- ---------- ----------
TEST              10      1280   --但分配空间并没有降低

move后，空闲块增加了，hwm降低了，但分配空间并没有降低。

使用alter table shrink space方式：

SQL> create table test(id number) storage(initial 10m next 1m) tablespace scott_test;
Table created.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> col segment_name for a10

SQL> select table_name,blocks,empty_blocks from user_tables where
 2  table_name='TEST';
TABLE_NAME    BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST               0        1280 

SQL> select segment_name,extents,blocks from user_segments

  2   where segment_name='TEST';

SEGMENT_NA    EXTENTS     BLOCKS

---------- ---------- ---------- ----------

TEST               10      1280

SQL> begin
 2    for i in 1..100000 loop
 3    insert into test values(i);
 4    end loop;
 5 end;
 6 /
PL/SQL procedure successfully completed.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables
 2 where table_name='TEST';
TABLE_NAME    BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST             186        1094

SQL> select segment_name,extents,blocks from user_segments
 2 where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS
---------- ---------- ----------
TEST              10      1280

SQL> delete from test where rownum<=50000;
50000 rows deleted.

SQL> select table_name,blocks,empty_blocks from user_tables
 2 where table_name='TEST';
TABLE_NAME    BLOCKS EMPTY_BLOCKS
----------------------- --------------- ---------------------
TEST             186        1094

SQL> select segment_name,extents,blocks from user_segments
 2 where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS
---------------------- ----------------- -----------------
TEST              10       1280

SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
        77

SQL> alter table test enable row movement;
Table altered.

SQL> alter table test shrink space;
Table altered.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> select table_name,blocks,empty_blocks from user_tables
  2  where table_name='TEST';
TABLE_NAME    BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST              79           9   --shrink space 后，空闲减少了， hwm 降低了

SQL> select segment_name,extents,blocks from user_segments
 2 where segment_name='TEST';
SEGMENT_NA   EXTENTS    BLOCKS
---------- ---------- ----------
TEST               1        88     -- 同时分配空间也降低了

通过上面比较，move不算真正意义上的压缩空间，只会压缩HWM以下的空间，消除碎片。shrink space真正做到了对段的压缩，包括初始分配的也压缩了，所以是hwm上和下的操作。根据需求来选择压缩方式。如果以后还会达到以前hwm高度，则选择move更合适。 

1、move可以通过制定storage参数做到真正压缩分配空间；

2、使用move时会改变一些记录的rowid，所以move后索引会变为无效，需要rebuild；

3、使用shrink space时，索引会自动维护。如果在业务繁忙时做压缩，可以先shrink space compact，来压缩，shrink space cascade会同时压缩索引。可以用alter index xxx shrink space；

4、shrink space需要在表空间是自动段空间管理的，所以system表空间上的表无法shrink space。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12615085/viewspace-747059/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12615085/viewspace-747059/