Andrew Fraser DBA

List oracle indexes and their columns with listagg

List indexes on large tables, with listagg to show the indexed columns in a comma separated line, and outer join to include tables with no indexes:

SELECT t.owner
     , t.table_name
     , ROUND ( t.blocks * 8 / 1024 / 1024 ) AS gb  -- assuming each block is default 8kb
     , t.num_rows
     , i.index_name
     , LISTAGG ( ic.column_name || ' , ' ) WITHIN GROUP ( ORDER BY ic.column_position ) AS ind_cols
     , i.index_type
     , i.uniqueness
     , t.partitioned AS table_partitioned
     , i.partitioned AS index_partitioned
  FROM dba_tables t
  LEFT OUTER JOIN dba_indexes i
    ON i.table_owner = t.owner
   AND i.table_name = t.table_name
  LEFT OUTER JOIN dba_ind_columns ic
    ON ic.index_owner = i.owner
   AND ic.index_name = i.index_name
 WHERE t.blocks > 1024 * 1024 / 8  -- 1gb, assuming each block is default 8kb
 GROUP BY t.owner
     , t.table_name
     , t.blocks
     , t.num_rows
     , i.index_name
     , i.index_type
     , i.uniqueness
     , t.partitioned
     , i.partitioned
 ORDER BY t.blocks DESC , t.owner , t.table_name , i.index_name
;