Many times customer would ask us that why more number of archive logs are being generated & flash recovery area (FRA) is getting filled with archive logs? What are the queries or sessions involved in generating more archive logs? below we can see the sql query to find which sessions and sql statements are generating more redo or more number of archive logs.... current sessions generating redo ================================= set lines 2000 set pages 1000 col sid for 99999 col name for a09 col username for a14 col PROGRAM for a21 col MODULE for a25 select s.sid,sn.SERIAL#,n.name, round(value/1024/1024,2) redo_mb, sn.username,sn.status,substr (sn.program,1,21) "program", sn.type, sn.module,sn.sql_id from v$sesstat s join v$statname n on n.statistic# = s.statistic# join v$session sn on sn.sid = s.sid where n.name like 'redo size' and s.value!=0 order by redo_mb desc; From the above query we can have the session and sql id which generates redo ...
Sharing real time work experience on oracle database administration, performance tuning activities etc..