[ORACLE] redo size

-------------------------------------------------------
-- name : show_redo
-- author : Kim, jongbum aka proud
-- description : redo size by process
-- usage : @show_redo
-- tested version : oracle 9i , 10g
-------------------------------------------------------

column osuser format a20
column username format a20
column module format a50

select max(decode(a.process,b.spid,to_number(null),a.sid)) sid
      ,max(decode(a.process,b.spid,to_number(null),a.serial#)) serial#
      ,max(decode(a.process,b.spid,null,a.process)) process
      ,a.osuser
      ,a.username
      ,a.command
      ,a.pdml_enabled
      ,a.module
      ,a.status
      ,decode(a.sql_hash_value,0,a.prev_hash_value,a.sql_hash_value) sql_hash_value
      ,sum(c.value) redo_size
  from v$session a
      ,v$process b
      ,v$sesstat c
      ,v$statname d
 where a.paddr = b.addr
   and a.sid = c.sid
   and c.statistic# = d.statistic#
   and d.name = 'redo size'
 group by a.osuser
         ,a.username
         ,a.command
         ,a.pdml_enabled
         ,a.module
         ,a.status
         ,decode(a.sql_hash_value,0,a.prev_hash_value,a.sql_hash_value)
/