Re: v$session question

On Thu, Jan 29, 2009 at 2:31 PM, Ram Raman <veeeraman@xxxxxxxxx> wrote:

> Listers,
>
> When I kill a session and the status shows as "KILLED" in v$session, I
> assumed that the rollback is taking place. I am not sure what is happening
> here:
>
>
> SYS@PROD> select sid,saddr, serial#, client_info,
> to_char(logon_time,'DD-MON-YY HH:MIam'), status
>   2  from v$session
>   3  where client_info like '%USER%'
>

If rollback is indeed happening, you can query v$transaction and watch it
change for the session.

select s.osuser
   ,s.username
   ,s.sid
   ,r.segment_name
   ,t.space
   ,t.recursive
   --,t.noundo
   -- rollback state
   -- thanks to Mark Powell for this
   -- RB is rolling back
   -- No RB is not rolling back
   , case when bitand(t.flag,power(2,7)) > 0 then 'RB'
      else 'No RB'
      end as rollback_status
   ,t.used_ublk
   ,t.used_urec
   ,t.log_io
   ,t.phy_io
   ,substr(sa.sql_text,1,200) txt
from v$session s,
   v$transaction t,
   dba_rollback_segs r,
   v$sql sa
where s.saddr=t.ses_addr
and   t.xidusn=r.segment_id(+)
and   s.sql_address=sa.address(+)
/

Jared

Other related posts: