Re: Sessions and Rollback Segments
Date: 1996/01/25
Message-ID: <4e8le6$hp2_at_gatekeeper.tasb.org>#1/1
I believe there are two things you could do:
- use set transaction statement in your application, so, you know which transaction is using which rollback segment before hand.
- 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