DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Thursday, November 12, 2015

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

1 comment:

Sridevi K said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.