分类： Oracle
2011-05-16 18:01:23
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> 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> select table_name,blocks,empty_blocks from user_tables where
2 table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 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;
Table analyzed.
SQL> select segment_name,extents,blocks from user_segments
2 where segment_name='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select table_name,blocks,empty_blocks from user_tables where
2 table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
--à插入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 segment_name,extents,blocks from user_segments where
2 segment_name='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select table_name,blocks,empty_blocks from user_tables where
2 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，而真正使用了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
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 segment_name,extents,blocks,initial_extent/1024/1024 init from user_segments where
2 SQL> segment_name='TEST';
SP2-0734: unknown command beginning "segment_na..." - rest of line ignored.
SQL> select segment_name,extents,blocks,initial_extent/1024/1024 init from user_segments where
2 segment_name='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> select table_name,blocks,empty_blocks from user_tables where
2 table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 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
2 ;
Table analyzed.
SQL> select segment_name,extents,blocks from user_segments
2 where segment_name='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select table_name,blocks,empty_blocks from user_tables
2 where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
SQL> delete from test where rownum<=50000;
50000 rows deleted.
SQL> select segment_name,extents,blocks from user_segments
2 where segment_name='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
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 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 segment_name,extents,blocks from user_segments
2 where segment_name='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 1 88
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降低
通过上面比较，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表空间上的表无法shrinkspace
原文来自：
