SELECT OBJNAME AS OBJNAME,
OBJTYPE AS OBJTYPE,
TO_CHAR(FRAGPCT) AS FRAGPCT
FROM (SELECT *
FROM (SELECT OWNER||'.'||INDEX_NAME AS OBJNAME,
'INDEX/INDEX PART' AS OBJTYPE,
ROUND(100.0*(1-INDEX_USED_PAGES(OWNER,INDEX_NAME)/1.0/INDEX_USED_SPACE(OWNER,INDEX_NAME)),2) FRAGPCT
FROM DBA_INDEXES
WHERE TABLESPACE_NAME NOT IN ('TEMP',
'ROLL',
'SYSTEM')
AND OWNER NOT IN ('SYS',
'SYSAUDITOR',
'SYSSSO',
'SYSJOB',
'SCHEDULER')
AND TEMPORARY='N'
AND INDEX_TYPE != 'CLUSTER'
AND INDEX_USED_SPACE(OWNER,INDEX_NAME)>(SELECT SUM(TOTAL_SIZE)* 0.0001 FROM V$DATAFILE)
ORDER BY INDEX_USED_SPACE(OWNER,INDEX_NAME) DESC)
ORDER BY FRAGPCT DESC LIMIT 10);

查看有没有碎片率高的索引，通过重建索引解决，没法避免除非索引字段数据稳定无变化