My sessions

A variation of sessions.sql, but limited to the current OS user account. Handy when there are a lot of sessions and you only want to see your own. I should probably write a generic one that takes parameters...

set linesize 119
col kill_id format a12 hea "ID for|KILL SESSION"
col username format a14 hea "Oracle|user"
col osuser format a8 hea "OS|user"
col used_ublk format 999990 hea "Undo|blocks"
col used_u_mb format 9,990.0 hea "Undo MB"
col used_urec format 999990 hea "Undo|records"
col session_status format a8 hea "Session|status"
col logged_on_for  format a11 hea "Logged on|for"
col logon_time hea "Session|started"
col transaction_status format a8 hea "Transaction|status"
col transaction_start format a11 hea "Transaction|started"
col type hea "Session|type"
col command format a20 trunc hea "Command"
col redo format a10 hea "      Redo"

break on report
comp sum label "" of used_u_mb redo_m on report

select --+ all_rows
       '''' || s.sid || ', ' || s.serial# || '''' kill_id
     , s.osuser
     , s.username
     , replace(replace(replace(replace(replace
       ( to_char(trunc(sysdate,'month') + (sysdate - s.logon_time), 'DD HH24:MI:SS')
       , '01 ', '00 '), '02 ', '01 '),'03 ','02 '),'04 ','03 '),'05 ','04 ') as logged_on_for
     , s.status as session_status
     , t.status as transaction_status
     , to_char(to_date(t.start_time,'MM/DD/RR HH24:MI:SS'),'HH24:MI:SS') as transaction_start
     , (t.used_ublk * db.block_size)/1048576 used_u_mb
     , case
       when sstat.value = 0 or sstat.value is null
            then to_char(null)
       when sstat.value < 1048576
            then lpad(to_char(greatest(sstat.value/1024,1),'999g999') || ' k',10)
       when sstat.value < 1073741824
            then lpad(to_char(sstat.value/1048576,'999g999') || ' m',10)
       else
            lpad(to_char(sstat.value/1073741824,'999g999') || ' g',10)
       end as redo
     , case
       when a.name = 'UNKNOWN'
            then null
       when a.name is not null
            then a.name
       when s.command = -67
            then 'MERGE'
       else
            to_char(s.command)
       end as command
from   ( select value as block_size
         from   v$parameter
         where  name = 'db_block_size' ) db
     , v$session s
     , audit_actions a
     , v$transaction t
     , v$sesstat sstat
     , v$statname n
where  s.username is not null
and    s.audsid != sys_context('userenv','sessionid')  -- Exclude this session
and    a.action (+)= s.command
and    t.addr (+)= s.taddr
and    sstat.sid (+)= s.sid
and    n.statistic# = sstat.statistic#
and    n.name = 'redo size'
order by s.logon_time, to_date(t.start_time,'MM/DD/RR HH24:MI:SS')
/

cl bre

Source code: //www.williamrobertson.net/code/sessions.sql