共计 4078 个字符，预计需要花费 11 分钟才能阅读完成。
这篇文章给大家分享的是有关 oracle 如何查询表碎片的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
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：表示碎片率百分比
感谢各位的阅读！关于“oracle 如何查询表碎片”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
