oracle查询碎片,Oracle日常巡检：Oracle表碎片的检查

SELECT A.OWNER,

A.SEGMENT_NAME,

round((B.NUM_ROWS * B.AVG_ROW_LEN)/1024/1024/1024,2) LEN,

round(SUM(A.BYTES)/1024/1024/1024,2) LEN_REAL,

round(SUM(A.BYTES)/1024/1024/1024 -

(B.NUM_ROWS * B.AVG_ROW_LEN)/1024/1024/1024,2) DIFF,

COUNT(1) "PARTITIONS"

FROM DBA_SEGMENTS A, DBA_TABLES B

WHERE a.owner = b.owner

AND A.SEGMENT_NAME = B.TABLE_NAME

AND (B.NUM_ROWS IS NOT NULL AND B.NUM_ROWS <> 0)

AND (B.AVG_ROW_LEN IS NOT NULL AND B.AVG_ROW_LEN <> 0)

/*AND A.BYTES/1024/1024/1024 -

(B.NUM_ROWS * B.AVG_ROW_LEN)/1024/1024/1024 > 0.1*/

AND B.OWNER LIKE 'U%'

GROUP BY A.OWNER,

A.SEGMENT_NAME,

(B.NUM_ROWS * B.AVG_ROW_LEN)/1024/1024/1024

ORDER BY DIFF DESC;