Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘Script to identify segments generating redologs’

Script to identify segments generating redologs

Posted by Pavan DBA on February 7, 2012

SELECT to_char(begin_interval_time,’YY-MM-DD HH24′) snap_time, 
        sum(db_block_changes_delta) BLOCK_CHANGED 
  FROM dba_hist_seg_stat dhss, 
       dba_hist_seg_stat_obj dhso, 
       dba_hist_snapshot dhs 
  WHERE dhs.snap_id = dhss.snap_id 
    AND dhs.instance_number = dhss.instance_number 
    AND dhss.obj# = dhso.obj# 
    AND dhss.dataobj# = dhso.dataobj# 
    AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
  GROUP BY to_char(begin_interval_time,’YY-MM-DD HH24′), 
  HAVING sum(db_block_changes_delta) > 0 
ORDER BY sum(db_block_changes_delta) desc ;

Posted in Administration, Scripts | Tagged: , , , , | Leave a Comment »

%d bloggers like this: