SELECT c.sid, c.username, rb.rbname rollback_name
FROM (SELECT d.addr rbaddr, a.name rbname
FROM v$rollname a, v$rollstat b, v$transaction d
WHERE a.usn = b.usn
AND b.usn = d.xidusn) rb,
v$session c
WHERE c.taddr = rb.rbaddr(+)
"Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message
news:l.1007397308.1180328369@[64.94.198.252]...
> Hi,folks
>
> I need a scripts which will give me output as follows
>
> SID username rollback_name
> 1 oracle
> 2 oracle
> .
> .
> .
> 13 scott RBS1
> 14 xyz
> 15 www RBS2
> etc, etc
>
> currently I have on like this
>
> select c.sid,
> c.username,
> a.name rollback_name
> from v$rollname a, v$rollstat b, v$session c, v$transaction d
> where
> a.usn = b.usn
> and b.usn = d.xidusn
> and c.taddr = d.addr
>
> but the above script only gives the result which w/o rollback as
> null, when I tried
>
> select c.sid,
> c.username,
> a.name rollback_name
> from v$rollname a, v$rollstat b, v$session c, v$transaction d
> where
> a.usn = b.usn
> and b.usn = d.xidusn(+)
> and c.taddr = d.addr(+)
>
> I got the error that ERROR at line 7:
> ORA-01417: a table may be outer joined to at most one other table
>
> could somebody help me out?
> Thanks for you help
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail subdomain of com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
Received on Tue Dec 11 2001 - 14:33:02 CST
