Re: Help needed :Rollback_seg STATUS = FULL

You do not have to bounce the database to get the FULL rollback segement status cleared (at least in 7.3.4). Just find the transaction using the rollback segment and either commit it or roll it back.

Note: If you roll it back, it may take time to clear, depending on how much information needs to be undone. I had this situation just a week ago or so, after killing the transaction, the rollback segment went to normal status after about 30 minutes.

thanks,

dave

In article <38565660.16551758_at_news.cis.dfn.de>,   stojentin_at_orgdv.din.de wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 14 1999 - 13:01:54 CST