Re: v$session question

From: Jared Still <jkstill_at_gmail.com>
Date: Fri, 30 Jan 2009 09:21:12 -0800
Message-ID: <bf46380901300921i5757ecbahf5c676def76dc28_at_mail.gmail.com>

On Thu, Jan 29, 2009 at 2:31 PM, Ram Raman <veeeraman_at_gmail.com> 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_at_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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 30 2009 - 11:21:12 CST