> 
> Hi,
> 
> Could you help me here.
> 
> This is my scenario.
> I have 8 large rollback segments and a few small rollback segments.
> I have an application that requires a large rollback segment. My
> application is using the SET TRANSACTION command to set to one of the
> eight large rollback segment. 
> Now, here is my problem.
> I have to pick the least used rollback segment for the SET TRANSACTION.
> How do i go about doing this.
> 
> Regards
> Geetha
> 
> Geetha Ramasubramanian
> E-mail: gramasub@transcendent.com
> Phone:  (201) - 475 - 4242
> 
Geetha,

You should pick the rollback segment with the fewest number of active
transactions. This information is kept in the v$rollstat view in the
xacts column. You will need to link it with the v$rollname view. Look at
the following SQL:

SELECT NAME, XACTS
FROM V$ROLLSTAT a, V$ROLLNAME b
WHERE a.usn=b.usn
ORDER BY XACTS
/

This will show each rollback segment and the number of active transactions
it is working on. Now, you will need to pick the lowest one, and there can
be several "tied" with the fewest XACTS. So, look at the following SQL:

SELECT NAME, XACTS
FROM V$ROLLSTAT a, V$ROLLNAME b
WHERE a.xacts in
   (SELECT MIN(c.xacts) FROM V$ROLLSTAT c) AND
    a.USN = b.USN AND
    ROWNUM < 2
/

The ROWNUM < 2 ensures that only 1 record is returned. You can also include
the 8 large rollback segments with an addiational "AND NAME IN ('RBBIG1',
'RBBIG2','RBBIG3','RBBIG4','RBBIG5','RBBIG6','RBBIG7','RBBIG8')" to ensure
that no small rollback segments are selected.

Best of luck,


-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 160+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             www.arikaplan.com                                 <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page HREF="../index.htm"> Back to Ari Kaplan's Home Page