Фрагментация таблиц в Oracle


--Фрагментация таблицы
SELECT TABLE_NAME, ROUND((BLOCKS*8),2) "SIZE_KB",
ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2) "SIZEREAL_KB",
ROUND((BLOCKS*8),2)-ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2) "RECLAIMABLE_SPACE",
ROUND((ROUND((BLOCKS*8),2)-ROUND((NUM_ROWS*AVG_ROW_LEN/1024),2))*100/DECODE(ROUND((BLOCKS*8),2), 0, 1, ROUND((BLOCKS*8),2)), 2) "PERC"
FROM ALL_TABLES
WHERE TABLE_NAME = upper('TABLE_NAME');

Способы дефрагментации:
1. drop and recreate (exp/imp)
2. truncate (exp the data, truncate it, imp the data)
3. alter TABLE move + rebuild indexes
4. SHRINK SPACE появилась в 10G
5. DBMS_REDEFINITION