Oracle Table Reorganization(shrink space)
Table Reorganization(shrink space)
標籤： Oracle
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://blog.xuite.net/charley_ocp/mydba01/36094404
Shrink 教學：http://dbworker.blogspot.tw/2011/06/oracle-segment-hwm-hight-water.html#more
Shrink 教學：http://dbworker.blogspot.tw/2011/06/oracle-segment-hwm-hight-water.html#more
