Oracle 表碎片清理

<pre name="code" class="sql">--碎片清理
DROP TABLE AAA;
create table aaa as select * from dba_objects;

--查询大小
select bytes/1024/1024,blocks from dba_segments a where a.segment_name='AAA';

BYTES/1024/1024     BLOCKS
--------------- ----------
	      9       1152
        
declare
begin
  for i in 1..10 loop
    delete from aaa;
    commit;
    insert into aaa select * from dba_objects;
    commit;
  end loop;
end;
/

--查询 segment 大小 80MB
select bytes/1024/1024,blocks from dba_segments a where a.segment_name='AAA';

--反复的delete insert 多出了那么多块
BYTES/1024/1024     BLOCKS
--------------- ----------
	     58       7424
       


--碎片清理              
alter table aaa enable row movement; 
alter table aaa shrink space cascade; 

--查询
select bytes/1024/1024,blocks from dba_segments a where a.segment_name='AAA';
--只有 8.3125 MB了
BYTES/1024/1024     BLOCKS
--------------- ----------
	 8.3125       1064