Query to Check Index Fragmentation (DBA_Indexes)

Hey everyone, below query is useful to quickly get an approximate fragmentation statistics for your database.

Although - the best method is to use ANALYZE INDEX and then checking INDEX_STATS table for exact status, below would still be useful to have an apprx result.

Variables
&owner <- Specify Main Schema (wildcard % for all schemas -- not recommended)
&tab <- Table Name (in case there is a specific table, use % for all tables in an schema)
&quality <- Lower is poor i.e. higher fragmentation (recommended 40 if you are unsure)

--Enjoy--

col num_rows FORMAT   999G999G999G999
col blocksize new_value blocksize noprint

set termout off
 SELECT to_number(SUBSTR(VALUE,1,40)) blocksize
   FROM sys.v_$parameter
  WHERE name = 'db_block_size';
set termout on

SELECT
  QUALITY,
  NUM_ROWS,
  INDEX_NAME,
  TABLE_NAME,
  last_analyzed,
  ROWLEN,
  LEAVES,
  NET_MB,
  GROSS_MB
FROM
( SELECT
    SUBSTR(I.INDEX_NAME, 1, 30) INDEX_NAME,
    SUBSTR(I.TABLE_NAME, 1, 30) TABLE_NAME,
    I.NUM_ROWS                  NUM_ROWS,
    i.last_analyzed,
    SUM(TC.AVG_COL_LEN + 1) + 7 ROWLEN,
    I.LEAF_BLOCKS               LEAVES,
    ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS / 1000000, 0) NET_MB,
    ROUND(I.LEAF_BLOCKS * (&blocksize -100 - 23 * NVL(I.INI_TRANS, 2)) *
      (1 - NVL(I.PCT_FREE, 10) / 100) / 1000000, 0)                GROSS_MB,
    ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /
      (I.LEAF_BLOCKS * (&blocksize -100 - 23 * NVL(I.INI_TRANS, 2)) *
      (1 - NVL(I.PCT_FREE, 10) / 100)) * 100, 0)                   QUALITY
  FROM DBA_INDEXES     I,
       DBA_IND_COLUMNS IC,
       DBA_TAB_COLUMNS TC
 WHERE I.INDEX_NAME   = IC.INDEX_NAME
   AND I.OWNER        = IC.INDEX_OWNER
   AND TC.TABLE_NAME  = IC.TABLE_NAME
   AND TC.OWNER       = IC.INDEX_OWNER
   AND TC.COLUMN_NAME = IC.COLUMN_NAME
   AND I.INDEX_TYPE   = 'NORMAL'
   AND I.LEAF_BLOCKS  > 1
   AND I.OWNER      like upper ( '&owner' )
   AND I.OWNER  not like upper ( 'SYS%' )
   and i.table_name like upper ( '&tab' )
   and i.num_rows   >  1000
 GROUP BY
    I.NUM_ROWS,
    i.last_analyzed,
    I.LEAF_BLOCKS,
    I.INDEX_NAME,
    I.TABLE_NAME,
    I.INI_TRANS,
    I.PCT_FREE
)
WHERE
  QUALITY < &quality
ORDER BY
  QUALITY;


Cheers!!
Anurag