Re: Sessions and Rollback Segments

I believe there are two things you could do:

1. use set transaction statement in your application, so, you know which transaction is using which rollback segment before hand.
2. use follwing sql statement to find out who is using which rollback segment dynamically (you can find more useful sql statements about rollback segment in the book " Oracle DBA hand book " by Kevin Loney):

       select r.name rollback_seg, 
              l.pid  oracle_pid,
              p.spid  system_pid,
              nvl(p.username, 'No Transaction') transaction,
              p.terminal
       from v$lock, l v$process p, v$rollname r
       where l.pid = p.pid(+) and
             tranc(l.id1(+)/65536) = r.usn  and
             l.type(+) = 'TX' and
             l.lmode (+) = 6
       order by r.name

good luck!
              

In article <4dor83$69b_at_radon>, Bill Eggers <beggers_at_efit.elcm.eds.com> writes:
> Hello,
>
> I have a large (about 800Gb) Database running in Oracle 7.2.2
> on Solaris with various people running ad hoc queries, batch
> jobs, etc. during various times. of the day. Occasionally,
> somebody will run a job which will cause a rollback segment to
> fill up to the point where other jobs cannot run properly.
> I used to solve the problem by killing all the non critical
> sessions, and then the job hitting the rollback segment would be
> dead. Then I could then shrink the rollback segment by hand. Now
> that does not work, because the job is not being run by one of these
> "non critical" session. I want to know which session I need to kill to
> free this rollback segment. Does anybody know how to associate a
> session with a particular rollback segment? I would be willing
> to buy some product that will do it for me.
>
> Thanks,
> Bill Eggers
> beggers_at_efit.elcm.eds.com
>
>
Received on Thu Jan 25 1996 - 00:00:00 CET