Re: Here's one for the Gurus: ROLLBACK SEGMENT/USERS

From: lange Francois <flange_at_pt.lu>
Date: 18 Jun 1998 12:49:48 GMT
Message-ID: <01bd9ab7$23eeff00$39d69ac2@fran-ois>

Hi,
Script find somewhere ?
Doc

      Name: rolbusrs.sql

      Author: Mark Gurry

      This script lists information on who is accessing the various rollbacks as of NOW!

#
column "Rollback Segment Name" format a18; column "Oracle User Session" format a40;

set pagesize 9999
ttitle 'Current Rollback Segment Usage' set heading off

spool &1/rolbusrs2.lis

select r.name "Rollback Segment Name",
       p.spid "Process ID", 
       s.username||'('||l.sid||')' "Oracle User Session",
       sq.sql_text

  from v$sqlarea sq, v$lock l, v$process p, v$session s, v$rollname r  where l.sid = p.pid(+)
   and s.sid = l.sid
   and trunc(l.id1(+) / 65536) = r.usn

   and l.type(+) = 'TX'
   and l.lmode(+) = 6
   and s.sql_address = sq.address
   and s.sql_hash_value   = sq.hash_value

 order by r.name
/

spool off
Regards
Homer J. Fong <homer_fong_at_hotmail.com> wrote in article <3588dfc3.13585885_at_twisto.eng.hou.compaq.com>...
> Hi out there,
> As I said, here is one for the gurus out there.
>
> I know about SET TRANSACTION USE ROLLBACK SEGMENT .....;
>
> But how do I see which user is using which rollback segment if they do
> not use this?
>
> Thanks!
>
>
>
>
>
Received on Thu Jun 18 1998 - 07:49:48 CDT