INDEX STATS TIME AND SIZE

------------------  INDEX STATS TIME AND SIZE  -------------------------------------------------

SELECT owner,index_name,table_name,num_rows,last_analyzed,status, v2.gb
  FROM dba_indexes v1,
    (SELECT segment_name,round(sum(bytes)/(1024*1024)) mb ,round(sum(bytes)/(1024*1024*1024)) gb
      FROM dba_segments
          WHERE owner like '%OWN%'
              AND segment_type in ('INDEX')
                GROUP BY  segment_name
                    ORDER BY 3 DESC)  v2
    WHERE v1.index_name=v2.segment_name
      AND owner LIKE '%OWN%'
      AND partitioned LIKE 'NO'
      AND last_analyzed < SYSDATE - 21
         ORDER BY gb, last_analyzed ;

----------------------------------------------------------------------------------------------

Best Regards 
Kamil Piórek