A query to show you current sessions generating redo


A query to show you current sessions generating redo:-

select s.sid, n.name, s.value, sn.username, sn.program, sn.type, sn.module
from v$sesstat s
  join v$statname n on n.statistic# = s.statistic#
  join v$session sn on sn.sid = s.sid
where name like '%redo entries%'
order by value desc;


Query V$SESS_IO. This view contains the column BLOCK_CHANGES which indicates
how much blocks have been changed by the session. High values indicate a 
session generating lots of redo.

SQL> SELECT s.sid, s.serial#, s.username, s.program,
     i.block_changes
     FROM v$session s, v$sess_io i
     WHERE s.sid = i.sid
     ORDER BY 5 desc, 1, 2, 3, 4;


Query V$TRANSACTION. This view contains information about the amount of
undo blocks and undo records accessed by the transaction (as found in the 
USED_UBLK and USED_UREC columns).

SQL> SELECT s.sid, s.serial#, s.username, s.program, 
     t.used_ublk, t.used_urec
     FROM v$session s, v$transaction t
     WHERE s.taddr = t.addr
     ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Comments

  1. Hi Shiven,

    Do you have any query which can give which were the sessions which generated more redo yesterday.

    Regards,
    Visith Kumar

    ReplyDelete

Post a Comment

Popular posts from this blog

finding fragmentation at table level and tablespace level in Oracle and steps to remove them

ORA-01153: an incompatible media recovery is active standby database oracle

ORA-16191: Primary log shipping client not logged on standby