Rollback Segment Usage 100%

Any suggestions as to implementing the initial=next extent sizes.

Thanks
Anurag

Based on previous discussion, I would suppose the following
storage parameters for rollback segments in that system:

INITIAL 512K
NEXT 512K
OPTIMAL 20M
MINEXTENTS 20
MAXEXTENTS 1000 (or what you like)

I would replace old rollback segments with new one by one, like this:

ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
DROP ROLLBACK SEGMENT rbs01;
CREATE ROLLBACK SEGMENT rbs01 TABLESPACE rbs
STORAGE (INITIAL 512K NEXT 512K OPTIMAL 20M MINEXTENTS 20 MAXEXTENTS 1000);

(Of course, you should not touch the SYSTEM rollback segment).

the docs of Admin Guide said that MINEXTENTS(n)=10 TO 20 is optimal and the rollback segment size(T) = max(table) * 10%, the INITIAL = T / n. so I think inosov's parameter will be appropriate.

Hi,

You can manually try to shrink the rollback segment by issuing

SVRMGRL>alter rollback segment < Your rollback seg name> shrink to 20M;

then

SVRMGRL> alter rollback segment storage(optimal 20M);

try this out.

rgds,

I appreciate INOSOV soutions.

Going by the size 0f 512k of extent size i think it should be large transactions used.

I would also suggest that the MINEXTENTS parameter
be set to 40 since you have mentioned on an average 20M.

By doing so dynamic extensions of extents can be avoided.

Cheers

Padmam

Mine is a Production Database. Is it safe to take one of the rollback segments offline them drop and recreate them.

Cant I create news rollback segments and drop the existing ones. Also what care should be taken to take the rollback segments offline. I mean do I need to check whether there are any active transactions or not.

Thanks
Anurag

Any suggestions please.

Sorry to bother again and again with the same question.

Thanks a lot for all the help.
Anurag

The safest time to rebuild rollback segments is when no active transactions.

If you take offline rollback segment before the transaction completing,
it may fail with ORA-1634.

Also, long-running queries may fail with "snapshot too old" error.

Thanks inosov,
How do I find whether any active transactions are there in the rollback segments.

Also to prevent ORA-1634 and snapshot too old errors shouldnt I first create additional rollback segments with your's and others suggestions in this thread and then drop the old ones.

Please suggest.

Thanks
Anurag

select s.sid, s.username, r.name
from v$session s, v$transaction t, v$rollname r
where s.taddr=t.addr
and t.xidusn = r.usn;

This will show all active transactions, users, and rollback segments.

If you receive "no rows selected", it's safe to rebuild rollback segments.