alter table move和shrink space的区别
alter table move跟shrink space的区别
都知道alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但move 跟shrink space还是有区别的。
Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
而shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。
也许很难理解吧，看测试就知道了。
SQL> select * from v$version;
BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> col TABLE_NAME for a10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
--TEST表初始分配了10M的空间，可以看到有10个EXTENTS，1280个BLOCKS。USER_TABLES视图显示有0个使用的BLOCKS，1280个空闲BLOCKS，即该10M空间内的BLOCK都还没被ORACLE”格式化”。
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 SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
--插入10W条数据后，分配的空间仍不变，因为10个EXTENTS还没使用完。显示使用了186个BLOCKS，空闲1094个BLOCKS。这时候的186BLOCKS即是高水位线
SQL> delete from test where rownum<=50000;
50000 rows deleted.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
77
--这边可以看到，删掉一半数据后，仍然显示使用了186个BLOCKS，高水位没变。但查询真正使用的BLOCK数只有77个。所以DELETE操作是不会改变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 where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 1199
--MOVE之后,HWM降低了，空闲块也上去了
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
--但是分配的空间并没有改变，仍然是1280个BLOCKS。下面看用SHRINK SPACE的方式
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 SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 1 88
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 7
--分配的空间已经降到最小，1个EXTENTS ，88个BLOCKS
所以MOVE并不算真正意义上的压缩空间，只会压缩HWM以下的空间，消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK)，也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩，包括初始分配的也压了，所以它是blow and above HWM操作。
至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的HWM高度，那显然MOVE是更合适的，因为SHRINK SPACE还需要重新申请之前放掉的空间，无疑增加了操作。
注意：
1.不过用MOVE的方式也可以做到真正的压缩分配空间，只要指定STORAGE参数即可。
SQL> drop table test;
Table dropped.
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
SQL> alter table test move storage (initial 1m);
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 16 128 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 128
2.使用move时，会改变一些记录的ROWID，所以MOVE之后索引会变为无效，需要REBUILD。
3.使用shrink space时，索引会自动维护。如果在业务繁忙时做压缩，可以先shrink space compact，来压缩数据而不移动HWM，等到不繁忙的时候再shrink space来移动HWM。
4.索引也是可以压缩的，压缩表时指定Shrink space cascade会同时压缩索引，也可以alter index xxx shrink space来压缩索引。
5.shrink space需要在表空间是自动段空间管理的，所以system表空间上的表无法shrink space。
都知道alter table move 或shrink space可以收缩段，用来消除部分行迁移，消除空间碎片，使数据更紧密，但move 跟shrink space还是有区别的。
Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
而shrink space 同样会移动高水位，但也会释放申请的空间，是在高水位上下(below and above HWM)都有的操作。
也许很难理解吧，看测试就知道了。
SQL> select * from v$version;
BANNER
----------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> col TABLE_NAME for a10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
--TEST表初始分配了10M的空间，可以看到有10个EXTENTS，1280个BLOCKS。USER_TABLES视图显示有0个使用的BLOCKS，1280个空闲BLOCKS，即该10M空间内的BLOCK都还没被ORACLE”格式化”。
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 SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
--插入10W条数据后，分配的空间仍不变，因为10个EXTENTS还没使用完。显示使用了186个BLOCKS，空闲1094个BLOCKS。这时候的186BLOCKS即是高水位线
SQL> delete from test where rownum<=50000;
50000 rows deleted.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
77
--这边可以看到，删掉一半数据后，仍然显示使用了186个BLOCKS，高水位没变。但查询真正使用的BLOCK数只有77个。所以DELETE操作是不会改变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 where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 1199
--MOVE之后,HWM降低了，空闲块也上去了
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
--但是分配的空间并没有改变，仍然是1280个BLOCKS。下面看用SHRINK SPACE的方式
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 SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 1 88
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 7
--分配的空间已经降到最小，1个EXTENTS ，88个BLOCKS
所以MOVE并不算真正意义上的压缩空间，只会压缩HWM以下的空间，消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK)，也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩，包括初始分配的也压了，所以它是blow and above HWM操作。
至于需要哪种方法，得看你的需求来了，需要分析表的增长情况，要是以后还会达到以前的HWM高度，那显然MOVE是更合适的，因为SHRINK SPACE还需要重新申请之前放掉的空间，无疑增加了操作。
注意：
1.不过用MOVE的方式也可以做到真正的压缩分配空间，只要指定STORAGE参数即可。
SQL> drop table test;
Table dropped.
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
SQL> alter table test move storage (initial 1m);
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 16 128 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 128
2.使用move时，会改变一些记录的ROWID，所以MOVE之后索引会变为无效，需要REBUILD。
3.使用shrink space时，索引会自动维护。如果在业务繁忙时做压缩，可以先shrink space compact，来压缩数据而不移动HWM，等到不繁忙的时候再shrink space来移动HWM。
4.索引也是可以压缩的，压缩表时指定Shrink space cascade会同时压缩索引，也可以alter index xxx shrink space来压缩索引。
5.shrink space需要在表空间是自动段空间管理的，所以system表空间上的表无法shrink space。