Shrink Table

說明

要調整 HWM 有以下方式可以做，各有不同優缺點：

1. export / import
   object id 與 data object id 會改變
2. alter table <table.name> move;
   data object id，rowid 會改變
3. alter index <index.name> rebuild online;
4. shrink table

主要步驟

 Shrink 指令：單一個 Table

SQL> alter table <owner>.<table_name> enable row movement;
SQL> alter table <owner>.<table_name> shrink space cascade;
SQL> alter table <owner>.<table_name> disable row movement; 

Shrink：多個 Tables

set heading off
set feedback off
set pagesize 0
set linesize 1000

SELECT 'alter table ' || OWNER || '.' || TABLE_NAME || ' enable row movement;' || chr(10) || 'alter table ' || OWNER || '.' || TABLE_NAME || ' shrink space cascade;' || chr(10) || 'alter table ' || OWNER || '.' || TABLE_NAME || ' disable row movement;' || chr(10)
FROM ALL_TABLES
WHERE OWNER = 'WIN_FDC_STDB'
AND (
TABLE_NAME NOT LIKE '%DEF%'
AND TABLE_NAME LIKE 'PROC%'
)
ORDER BY NUM_ROWS DESC, TABLE_NAME ASC;

參考連結

• http://database.51cto.com/art/201108/284425.htm
• http://www.oracle.com/technetwork/is...ng-096075.html
• http://luhartma.blogspot.tw/2006/04/...in-oracle.html