oracle查询表碎片

jason0401

　　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：表示碎片率百分比