Table 在大量刪除歷史紀錄後,原先使用的儲存空間並不會自動被釋出,而是要經過一個 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;