Unused Objects Report | Oracle | Unused Indexes | Unused Table Segments | Oracle

Unused Objects Report

SELECT a.owner,a.object_name,a.object_type,subobject_name ,b.su,c.bytes/(1024*1024) MBYTES 
  FROM dba_objects a,
       dba_segments c, 
       (
  SELECT obj#,
  ------------------
  Sum(LOGICAL_READS_DELTA)+Sum(DB_BLOCK_CHANGES_TOTAL)+Sum(PHYSICAL_READS_TOTAL) AS su
  ------------------
    FROM dba_hist_seg_stat
  ------------------
            GROUP BY obj# 
  ------------------
              HAVING (Sum(LOGICAL_READS_DELTA)+Sum(DB_BLOCK_CHANGES_TOTAL)+Sum(PHYSICAL_READS_TOTAL))<10 
                ) b 
  WHERE a.object_id=b.obj# 
    AND a.object_name=c.segment_name
    --AND OBJECT_TYPE LIKE 'INDEX' 
    AND a.owner NOT LIKE 'SYS'
      ORDER BY 6 DESC ;

SELECT Sum(value),object_name 
FROM v$segment_statistics WHERE OBJECT_TYPE LIKE '%INDEX%' AND statistic_name LIKE '%reads%' GROUP BY object_name HAVING Sum(VALUE) < 10  

Best Regards

Kamil Piórek

kamil.piorekk@gmail.com