空间碎片度检查脚本

表空间碎片度：
select tablespace_name,sqrt(max(blocks)/sum(blocks))*
(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name order by 1;


需要重组的表：
-- 各列的说明：
-- WASTE_PER：已分配空间中水线以下的空闲空间(即浪费空间)的百分比。
-- TABLE_KB：该表目前已经分配的所有空间的大小，以k为单位。
-- NUM_ROWS：在在表中数据的行数
-- BLOCKS：该表目前已经分配的数据块的块数，包含水线以上的部分
-- EMPTY_BLOCKS：已分配空间中水线以上的空闲空间
-- HIGHWATER_MARK：目前的水线
-- AVG_USED_BLOCKS：理想情况下(没有行迁移)，该表数据应该占用的数据块的个数
-- CHAIN_PER：发生行迁移现象的行占总行的比率
-- EXTENTS：该表目前已经分配的extent数
-- MAX_EXTENTS：该表可以分配的最大extent的个数
-- ALLO_EXTENT_PER：目前已分配的extent的个数占可以分配最大extent的比率
-- CAN_EXTEND_SPACE：是否可以分配下一个extent
--N EXT_EXTENT：下一个extent的大小
-- MAX_FREE_SPACE：表的已分配空间中最大的空闲空间
 
-- 此脚本可以查询出浪费空间的表(浪费超过25%)，而且还计算出其它信息(使用时根据
-- 具体情况修改owner条件：
 
SELECT owner, segment_name table_name, segment_type,
GREATEST (ROUND ( 100
* ( NVL (hwm - avg_used_blocks, 0)
/ GREATEST (NVL (hwm, 1), 1)
),
2
),
0
) waste_per,
ROUND (BYTES / 1024, 2) table_kb, num_rows, blocks, empty_blocks,
hwm highwater_mark, avg_used_blocks, chain_per, extents, max_extents,
allo_extent_per,
DECODE (GREATEST (max_free_space - next_extent, 0),
0, 'N',
'Y'
) can_extend_space,
next_extent, max_free_space, o_tablespace_name tablespace_name
FROM (SELECT a.owner owner, a.segment_name, a.segment_type, a.BYTES,
b.num_rows, a.blocks blocks, b.empty_blocks empty_blocks,
a.blocks - b.empty_blocks - 1 hwm,
DECODE (ROUND ( ( b.avg_row_len
* num_rows
* (1 + (pct_free / 100))
)
/ c.BLOCKSIZE,
0
),
0, 1,
ROUND ( ( b.avg_row_len
* num_rows
* (1 + (pct_free / 100))
)
/ c.BLOCKSIZE,
0
)
)
+ 2 avg_used_blocks,
ROUND ( 100
* ( NVL (b.chain_cnt, 0)
/ GREATEST (NVL (b.num_rows, 1), 1)
),
2
) chain_per,
ROUND (100 * (a.extents / a.max_extents), 2) allo_extent_per,
a.extents extents, a.max_extents max_extents,
b.next_extent next_extent,
b.tablespace_name o_tablespace_name
FROM SYS.dba_segments a, SYS.dba_tables b, SYS.ts$ c
WHERE a.owner = b.owner
AND segment_name = table_name
AND segment_type = 'TABLE'
AND b.tablespace_name = c.NAME
UNION ALL
SELECT a.owner owner, segment_name || '.' || b.partition_name,
segment_type, BYTES, b.num_rows, a.blocks blocks,
b.empty_blocks empty_blocks,
a.blocks - b.empty_blocks - 1 hwm,
DECODE (ROUND ( ( b.avg_row_len
* b.num_rows
* (1 + (b.pct_free / 100))
)
/ c.BLOCKSIZE,
0
),
0, 1,
ROUND ( ( b.avg_row_len
* b.num_rows
* (1 + (b.pct_free / 100))
)
/ c.BLOCKSIZE,
0
)
)
+ 2 avg_used_blocks,
ROUND ( 100
* ( NVL (b.chain_cnt, 0)
/ GREATEST (NVL (b.num_rows, 1), 1)
),
2
) chain_per,
ROUND (100 * (a.extents / a.max_extents), 2) allo_extent_per,
a.extents extents, a.max_extents max_extents, b.next_extent,
b.tablespace_name o_tablespace_name
FROM SYS.dba_segments a,
SYS.dba_tab_partitions b,
SYS.ts$ c,
SYS.dba_tables d
WHERE a.owner = b.table_owner
AND segment_name = b.table_name
AND segment_type = 'TABLE PARTITION'
AND b.tablespace_name = c.NAME
AND d.owner = b.table_owner
AND d.table_name = b.table_name
AND a.partition_name = b.partition_name),
(SELECT tablespace_name f_tablespace_name,
MAX (BYTES) max_free_space
FROM SYS.dba_free_space
GROUP BY tablespace_name)
WHERE f_tablespace_name = o_tablespace_name
AND GREATEST (ROUND ( 100
* ( NVL (hwm - avg_used_blocks, 0)
/ GREATEST (NVL (hwm, 1), 1)
),
2
),
0
) > 25
AND owner = 'Schema Name'
AND blocks > 128
ORDER BY 4 DESC, 5 DESC;