How to find the object that is causing excessive redo or archive generation between a given date. You can get from the v$log_history but for history you will need to query the awr history views as below.
SELECT dhso.object_name,
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('2012_05_02 04','YYYY_MM_DD HH24') AND to_date('2012_05_02 07','YYYY_MM_DD HH24')
GROUP BY dhso.object_name
order by sum(db_block_changes_delta) desc
/OBJECT_NAME SUM(DB_BLOCK_CHANGES_DELTA)
------------------------------ ---------------------------
XXXXXXX 315744448
Find the statement related to it.
SELECT distinct dbms_lob.substr(sql_text,4000,1)
FROM dba_hist_sqlstat dhss,
dba_hist_snapshot dhs,
dba_hist_sqltext dhst
WHERE upper(dhst.sql_text) LIKE '%XXXXXXXXXX%'
AND dhss.snap_id=dhs.snap_id
AND dhss.instance_Number=dhs.instance_number
AND dhss.sql_id = dhst.sql_id and rownum<2;DBMS_LOB.SUBSTR(SQL_TEXT,4000,1)
--------------------------------------------------------------------------------
update XXXXXXXX set LATEST_VERSION=:1 where ID=:2 and VERSION<:3SQL> SQL>
Hope this helps!!!
-Thanks
Geek DBA
I have recently started a website, the info you offer on this web site has helped me tremendously. Thanks for all of your time & work.
Hello
You are welcome.