SELECT OWNER,
TABLE_NAME,
SEGMENT_TYPE,
segment_space_management MANAGEMENT,
TABLE_MB USED_MB,
ROUND(WASTE_PER * TABLE_MB / 100, 2) FRAG_MB,
WASTE_PER fragment_per,
LAST_ANALYZED
FROM (SELECT OWNER,
SEGMENT_NAME TABLE_NAME,
LAST_ANALYZED,
SEGMENT_TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) / GREATEST(NVL(HWM, 1), 1)), 2), 0) WASTE_PER,
ROUND(BYTES / POWER(1024, 2), 2) TABLE_MB,
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,
block_size,
segment_space_management
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))) / dt.block_size, 0), 0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE / 100))) / dt.block_size, 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,
B.LAST_ANALYZED,
dt.block_size,
DT.segment_space_management
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
dba_tablespaces dt
WHERE A.OWNER = B.OWNER
and SEGMENT_NAME = TABLE_NAME
and SEGMENT_TYPE = 'TABLE'
-- and dt.segment_space_management = 'AUTO'
-- and B.table_name='LS_REPORT_VALUE_COLLECT'
and dt.tablespace_name = a.tablespace_name
--and b.last_analyzed > to_date('20070601', 'yyyymmdd')
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))) / dt.block_size, 0), 0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE / 100))) / dt.block_size, 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,
d.last_analyzed,
dt.block_size,
DT.segment_space_management
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TAB_PARTITIONS B,
SYS.DBA_TABLES D,
dba_tablespaces dt
WHERE A.OWNER = B.TABLE_OWNER
and SEGMENT_NAME = B.TABLE_NAME
and SEGMENT_TYPE = 'TABLE PARTITION'
-- and dt.segment_space_management = 'AUTO'
-- and B.table_name='LS_REPORT_VALUE_COLLECT'
and dt.tablespace_name = a.tablespace_name
AND D.OWNER = B.TABLE_OWNER
AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME,
--AND D.last_analyzed > to_date('20070601', 'yyyymmdd')),
(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 not in ('SYS', 'SYSMAN')
AND BLOCKS > POWER(1024, 2) / block_size)
where ROUND(WASTE_PER * TABLE_MB / 100, 2) > 100
ORDER BY 7 DESC;
USED_MB：表示对象已使用大小
FRAG_MB：表示碎片所占大小
FRAGMENT_PER：表示碎片率百分比
