Re: Rollback Segments in use by a session?

From: Doug Carter <dcarter_at_tui.com.au>
Date: Thu, 9 Apr 1998 15:09:01 +1000
Message-ID: <6ghl64$lic$1@reader1.reader.news.ozemail.net>

Hi Saad,

>Is there a way to find out what rollback segments
>are being used by a session

This is not exactly what you want - but the logic should be enough to point you in the right direction. The following displays session and rbs segment details for sessions with locks in a rollback segment.

select l.sid,
       l.type,
       s.username,
       r.owner,
       r.segment_name,
       l.lmode,
       l.request,
       l.id1,
       l.id2

 from dba_rollback_segs r, v$lock l, v$session s where s.sid = l.sid
and trunc (l.id1(+)/65536) = r.segment_id /

I don't have a database handy - but you might be able to use v$resource as a source for id1.

Cheers

Doug Carter
Database Administrator
TUI Consulting Received on Thu Apr 09 1998 - 00:09:01 CDT