set lines 132 pages 2000
col sid form 9999
col username form a10
col value Head "Redo | Generated in | MB" form 9999999999.99
col program form a20
col logtime head "Logon Time" form a15
col sql_text format a45 wrap
select a.sid, a.username, a.logtime, a.program, a.value, sq.sql_text from (SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi') logtime, nvl(se.program,se.module) program, round((value/1048576),2) VALUE, se.sql_address FROM v$sesstat st, v$statname sn, v$session se WHERE sn.name = 'redo size' AND value > 104857600 AND sn.statistic# = st.statistic# AND st.sid = se.sid ORDER BY 5 desc ) a, v$sql sq where a.sql_address=sq.address and rownum < 11 order by 5 desc;
No comments:
Post a Comment