检查oracle碎片,oracle查询表碎片

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