檢查tablespace破碎空間

vean0803i

select tablespace_name ,
   sqrt(MAX(blocks)/SUM(blocks))*(100/sqrt(sqrt(count(blocks)))) as "FSFI%"
from dba_free_space
group by tablespace_name
order by 1;

      tablespace:表格空間名稱

FSFI:可用破碎空間索引(Free Space Fragmention Index)，數值最大100，表示完全沒有破碎空間，數值越低代表空間的碎片越嚴重，通常低於30%就需要進行重整作業(Recorganization Job)，這是資料庫管理員是非常重要的例行作業。


首先修正SYSAUX
COALESCE

For each datafile in the tablespace, this clause combines all contiguous free extents into larger contiguous extents. Please use Locally managed tablespace, it will automatically take care of this issue.

DEALLOCATE UNUSED
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses003.htm#g1056929



10G新功能


alter table mytable enable row movement;

alter table xxx shrink space compact