Rollback Segments in use by a session?

[Saad Ahmad]

Hello !!

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

Thanks
Saad Ahmad
saad....@mchugh.com

[Doug Carter]

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

[Andy Tasker]

Saad Ahmad wrote in message
<01bd62fb$5ffbe920$2207...@sahmad-pc.mfa.com>...
>Hello !!

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

>Thanks
>Saad Ahmad
>saad....@mchugh.com

the dynamic table v$transaction has what you need.

I haven't a database at the mo, but the query goes something like
this :

select s.sid, r.name
from v$session, v$transaction, v$rollname
where s.sid = <sid>
and t.addr = s.taddr
and r.usn = t.xidusn -- can't quite remember the col; but it's got usn it in
!

Andy T.

[cmo...@iname.com]

If your question eactly means what it asks,
then the only view you need is v$rollname.

select * from v$rollname; -- This will list all rollback segments, currently
online, available for that session.

If you want to know what rollback segments are available for the database,
issue the following SQL. This will list all online and offline rollback
segments and you may bring the offline RBSs online.

select segment_name, status from dba_rollback_segs;

Hope this helps,
CM

In article <01bd62fb$5ffbe920$2207...@sahmad-pc.mfa.com>,

"Saad Ahmad" <saad....@mchugh.com> wrote:
>
> Hello !!
>
> Is there a way to find out what rollback segments are being used
> by a session
>
> Thanks
> Saad Ahmad
> saad....@mchugh.com
>

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading

[Gatjiek Kuach]

On Thursday, April 9, 1998 at 9:00:00 AM UTC+2, Doug Carter wrote:
> 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

Dear Doug Carter,

I am Gatjiek Kuach, I am South Sudanese working with Telecommunication company as Oracle Database Administrator, I have been given task by my boss to always "Display database sessions using rollback segments" I have been trying many queries from different guys, it didn't work, but your script help me a lot with result that I mostly need.

Thank so much for devoting your time with that script.
Respectfully,
Gatjiek Kuach
Database Administrator
Digitel Telecom.