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完才看得出來效過)：
查詢那一些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
