Object list where more block changes happened – Data based on awr

/* Object List where more block changes happened. This will help us to find more archive generation based on which object */

set pages 10000
set lines 170
col object_name format a40
col object_type format a30

SELECT dhso.object_name, object_type, SUM (db_block_changes_delta)
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 (’20-10-2015 00:00:00′, ‘DD-MM-YYYY HH24:mi:ss’) AND TO_DATE (’29-10-2015 23:59:59′, ‘DD-MM-YYYY HH24:mi:ss’)
GROUP BY dhso.object_name, object_type
HAVING SUM (db_block_changes_delta) > 0
ORDER BY 2, SUM (db_block_changes_delta) DESC
/

  • November 11, 2015 | 19 views