alter table move和shrink space 区别.docxVIP

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 - ProdPL/SQL Release 10.2.0.4.0 - ProductionCORE 10.2.0.4.0 ProductionTNS for Linux: Version 10.2.0.4.0 - ProductionNLSRTL 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 a10SQL 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 10SQL select table_name,blocks,empty_blocks from user_tables where 2 table_name=TEST;TABLE_NAME BLOCKS EMPTY_BLOCKS---------- ---------- ------------TEST 0 1280SQL 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为186SQL 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