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;
