Re: Help needed :Rollback_seg STATUS = FULL

On Tue, 14 Dec 1999 13:38:16 +0100, "Reiner Neumann" <reiner.neumann_at_messe.de> wrote:

>Three of my eight rollback_segs are having the Status 'FULL'.
>The tablespace is not full, there are not too much extents
>and there is nothing in my alert-file.
>
>Do i have to do something ???

Hello Reiner,

yes, you should do something. The Status 'FULL' will only be reset automatically after database startup.

First you can try to increase the maximum number of extents for the rollback segments:
  SQL> alter tablespace $RBS default storage (maxextents $EXT); where $RBS is the name of your rollback segment tablespace and $EXT is a higher number of extents than now.

This would not change the status of the FULL rollback segments, but avoid that more of them will become full.

Next you should change the status of the full rollback segments:   SQL> alter rollback segment $NAME offline; where $NAME is the name of the rollback segment. You should do this for each full rollback segment. The status will change to 'PENDING OFFLINE' until all running transactions which are using these segments terminate. After all of them terminated (status changed to 'OFFLINE') you can bring the rollback segments online again and they will shrink.   SQL> alter rollback segment $NAME online;

When the sutiation is normal, try to find the transaction(s) which filled up the rollback segments and change the application that it uses a special designed rollback segment (named like xlarge) for such long running or large transactions:

Directly after a commit or rollback you can set the rollback segment for the next transaction with:
  SQL> commit;
  SQL> alter session set transaction use rollback segment $XLARGE;

Fred Stojentin Received on Tue Dec 14 1999 - 08:53:32 CST