共計(jì) 4078 個(gè)字符，預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。
這篇文章給大家分享的是有關(guān) oracle 如何查詢表碎片的內(nèi)容。丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨丸趣 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：表示碎片率百分比
感謝各位的閱讀！關(guān)于“oracle 如何查詢表碎片”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！
