Oracle 10g Online Segment Shrinking 測試

Propose / 目標

1.move down the HWM (表格如果常異動 insert/update/delete，則會產生許多碎片)

2.releases unused extents.

Howto:

alter table mytable enable row movement;

alter table mytable shrink space compact;

測試:
建立一個表格，並insert 資料，delete後會發現資料表格其實還占有extent空間...須用shrink space 語法才能縮得回來.

create table test.test (A date , B number (10));

col segment_name for a20

col extent_id for 99

select segment_name , extent_id from dba_extents where segment_name like 'TEST' and owner='TEST';

Insert 90000 records…

begin

   for v_number in 10000..100000 loop

        insert into test.test values (sysdate , v_number);

        commit;

     end loop;

end;

/

Verify extents:

col segment_name for a20

col extent_id for 99

select segment_name , max(extent_id) , min(extent_id) from dba_extents where segment_name like 'TEST' and owner='TEST' group by segment_name;

Verify TABLE HWM:

SELECT BLOCKS

FROM   DBA_SEGMENTS

WHERE  OWNER=UPPER('test') AND SEGMENT_NAME = UPPER('test');

計算Table size

analyze table test.test compute statistics;

select

table_name,(blocks*8)||'kb' "size" from all_tables where table_name = 'TEST' and owner='TEST';

刪除所有資料

select segment_name , extent_id from dba_extents where segment_name like 'TEST' and owner='TEST';

delete from test.test;

commit;

計算Table size (with fragmentation) , 此時Table 的data segment 就像一個空的容器，實際上並無資料.

analyze table test.test compute statistics;

select sum(bytes)/1024 from dba_extents where owner='TEST';

Starting table shrinking:

alter table test.test enable row movement;

alter table test.test shrink space;

select segment_name , extent_id from dba_extents where segment_name like 'TEST' and owner='TEST';

Verify TABLE HWM again:

SELECT BLOCKS

FROM   DBA_SEGMENTS

WHERE  OWNER=UPPER('test') AND SEGMENT_NAME = UPPER('test');

Q:為何無資料仍佔用8 oracle blocks(64kb)??

    

A:因為表格初建的語法如下, 會先預留64kb給表格

  ........STORAGE(INITIAL 65536…)