Index Fragmentation

SELECT 

    index_name,

    table_name,

    leaf_blocks,

    empty_blocks,

    num_rows,

    DISTINCT_KEYS,

    (leaf_blocks - empty_blocks) AS used_blocks,

    ROUND((empty_blocks / leaf_blocks) * 100, 2) AS empty_block_percent

FROM 

    dba_indexes

WHERE 

    owner = UPPER('<YOUR_SCHEMA_NAME>')

    AND index_type = 'NORMAL' -- filter for B-tree indexes

    AND leaf_blocks > 0 -- ensure that only indexes with leaf blocks are evaluated

ORDER BY 

    empty_block_percent DESC;