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,¬†
        dhso.object_name, 
        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′),¬†
           dhso.object_name 
  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: