Oracle Table Reorganization(shrink space)

Table Reorganization(shrink space)

標籤: 
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 <cascade>;
 
alter table mytable shrink space <cascade>;
 


 

Table Reorganization
1.static (table不能被DML)
 1.1 export -> drop -> import =>object id與data object id改變
 1.2 alter table hr.big1 move; => data object id變更
2.dynamic (table可以被DML)
 2.1 alter table hr.big1 shrink space compact; -> alter table hr.big1 shrink space; =>object id與data object id都沒有改變
 2.2 table ondefinition => object_id與data_object_id都變更,但其實是object_name變更才對.
 

要調整HWM可以藉由下面的指令達成
 
1. export / import 
2. alter table table_name move; (index會失效,MVIEW使用rowid會失效)
3. alter index index_table rebuild online; 
4. alter table table_name shrink space ; (only for Oracle 10g)
 
Shrink Space Syntax
 
shrink space 的兩個必要條件 
1. Enable row movement 
2. Table 所在的tablespace 不能使用在segment space management manaual 的tablespace, 必須為auto
 
執行shrink space 分成兩個階段 
1. compact : 透過insert / delete 將資料盡量排例在segment 前面.這個階段會造成rowid的改變,因此需要enable row movement.
 
alter table TABLE_NAME enable row movement;
 
2. HWM 調整:這個階段是調整HWM位置,釋放表格空間
 
alter table <TABLE_NAME> shrink space compact ; 只會執行第一階段 
alter table <TABLE_NAME> shrink space ; 兩個階段都會執行 
alter table <TABLE_NAME> shrink space cascade ; 同時處理相關index 空間 
alter index <INDEX_NAME> shrink space ; 回收index 空間 
alter table <TABLE_NAME> modify lob (<lob_clomun>) (shrink space);
 
 
由於alter table TABLE_NAME enable row movement 可能會使的引用該table的object( ex procedure,package,view…) 變成invalid,所以執行後最後執行一下utlrp.sql 或 utlprp.sql 來編譯一下invalid object , utlrp.sql 或 utlprp.sql放在$ORACLE_HOME/rdbms/admin 目錄下,或者執行UTL_RECOMP這個package
 
Shrink space script 
 
1. Normal Table
select'alter table '|| owner || '.' || table_name||' enable row movement;
'||chr(10)||'alter table '|| owner || '.'||table_name||' shrink space;'||chr(10)
from  dba_tables;
 
select'alter index '|| owner || '.' ||index_name||' shrink space;'||chr(10) from dba_indexes;
 
2. Partition Table
select 'alter table '|| owner || '.'  ||table_name||' enable row movement;'||chr(10)||
'alter table '|| owner || '.'||table_name||' shrink space;'||chr(10) from dba_tables
 
select 'alter index '|| owner || '.'||index_name||' shrink space;'||chr(10)
from dba_indexes where uniqueness='NONUNIQUE' ;
 
select 'alter table '|| owner || '.'||segment_name||' modify subpartition '||partition_name||' shrink space;'||chr(10)
from dba_segments where segment_type='TABLE SUBPARTITION' ;

 

 
————————————————————-
測試環境建置:
SQL>create table hr.test (A date , B number (10));
Insert 90000 records…
SQL>begin
   for v_number in 10000..100000 loop
        insert into hr.test values (sysdate , v_number);
        commit;
     end loop; 
end;
/
 
確認一下此Table的資料型態(要看一下搬移的資料難度,若都是Table的話,要搬移的話比較方便)
SQL>select segment_type , count(1) from dba_segments where tablespace_name=' users' group by segment_type;
 
 
計算Table size(刪除前的資料)
SQL>analyze table hr.test compute statistics;
SQL>select table_name,(blocks*8)||'kb' "size" from all_tables where table_name = 'TEST' and owner='HR';
 
刪除所有資料(這個時候空間還沒有Release出來,現在只是一個空的空間)
SQL>select segment_name , extent_id from dba_extents where segment_name like 'TEST' and owner='HR';
SQL>delete from hr.test;
SQL>commit;
 
Starting table shrinking:
SQL>alter table hr.test enable row movement;
SQL>alter table hr.test shrink space compact;
–僅透過delete/insert row的方式,將row搬移到更接近segment header的blocks,試圖製造出high water mark下有連續的empty blocks
–因為使用shrink space compact,並沒有移動high water mark與並沒有收回空間,所以table還可以被其他人進行dml,query
 
SQL>alter table hr.test shrink space;
–僅透過delete/insert row的方式,將row搬移到更接近segment header的blocks,試圖製造出high water mark下有連續的empty blocks
–調整high water mark的位置到目前有資料的最後一個block,並將新的high water mark之上的空間,全數收回,變成free extents,歸還tablespace
–不過因為shrink space最後將變更high water mark位置與收回空間,所以在此操作過程中,table不能被其他session進行ddl/dml指令
 
SQL> alter table hr.test shrink space cascade;
 –假設有index,透過cascade將table之上的index也一起進行shrink segment操作
 
SQL>select segment_name , extent_id from dba_extents where segment_name like 'TEST' and owner='HR';
 
計算Table size(刪除後的資料)
SQL>analyze table hr.test compute statistics;
SQL>select table_name,(blocks*8)||'kb' "size" from all_tables where table_name = 'TEST' and owner='HR';
Q: 為何無資料仍佔用8 oracle blocks(64kb)??
A:因為建立TABLE時,會先預留給64KB給表格

查詢那一些db file可以縮小空間(Shrink完才看得出來效過):

 
SQL>SELECT
  a.file_id,
  a.file_name
  file_name,
  CEIL( ( NVL( hwm,1 ) * blksize ) / 1024 / 1024 ) smallest,
  CEIL( blocks * blksize / 1024 / 1024 ) currsize,
  CEIL( blocks * blksize / 1024 / 1024 ) –
  CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) savings,
  'alter database datafile ''' || file_name || ''' resize ' ||
  CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
FROM
  DBA_DATA_FILES a,
  (
     SELECT  file_id, MAX( block_id + blocks – 1 ) hwm
     FROM    DBA_EXTENTS
     GROUP BY file_id
  ) b,
  (
     SELECT TO_NUMBER( value ) blksize
     FROM   V$PARAMETER
     WHERE  name = 'db_block_size'
  )
WHERE
  a.file_id = b.file_id(+)
AND
  CEIL( blocks * blksize / 1024 / 1024 ) – CEIL( ( NVL( hwm, 1 ) * blksize ) / 1024 / 1024 ) > 0
ORDER BY 5 desc
 
Reference:
http://jaychu649.blogspot.tw/2011/11/oracle-10g-online-segment-shrinking.html
 
http://blog.xuite.net/charley_ocp/mydba01/36094404
Shrink 教學:http://dbworker.blogspot.tw/2011/06/oracle-segment-hwm-hight-water.html#more