RE: get rollback info for process

Don't remember exactly where I found this(possibly MetaLink), but it sure works.

SELECT r.name, b.extents, l.sid,p.spid,s.username,

               s.machine, s.program
        FROM v$lock l, dba_segments b, v$process p, v$rollname r,
v$session s
        WHERE  b.segment_name = r.name
		  and l.sid = p.pid(+)
          AND s.sid=l.sid
          AND TRUNC (l.id1(+)/65536) = r.usn
          AND l.type(+) = 'TX'
          AND l.lmode(+) = 6
        ORDER BY r.name 

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Doug Gernaat Sent: Tuesday, May 30, 2006 11:04 AM
To: oracle-l_at_freelists.org
Subject: get rollback info for process

we have a ProC program that runs for about 90mins. after its completion, i would like to be able to identify which rollback segments it used. a trace/tkprof didn't help me(maybe i overlooked). statspak? any ideas?

thanks
-doug-
--

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 30 2006 - 11:51:49 CDT