Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

What happens when a rollback segment gets "FULL"

SOLVED
Go to solution
Morten Kristiansen
Frequent Advisor

What happens when a rollback segment gets "FULL"

Hi,

I'm wondering what happens with a job using alot of space on a rollback segment in a locally managed rollback tablespace (not an undo tablespace, please don't ask why undo isn't used)?

We have had a couple of happenings like this and it seems that bouncing the database is the only way of changing the status to "online" again. I've tried to add space to the rollback tablespace, but nothing seems to happen.

What do the job that's filling the tablespace do when the space is used? Does it terminate or will it go into a "wait" state? Is a rollback trigged?

Does anybody knows how oracle tries to resolve this?

regards
Morten K
5 REPLIES
Peter Godron
Honored Contributor

Re: What happens when a rollback segment gets "FULL"

Morten,
the easiest to understand info I have seen was:
http://ist.uwaterloo.ca/~baumbach/ORACLEnotes/MISC_rbs_explained.html

If the transaction runs out of rollback tablespace an oracle error (ORA-1650)occurs, which can be resolved with a "alter tablespace add datfile".
If the transaction runs out of segments space it will create new segment extents until max_extents is reached or tablespace is full.

Morten Kristiansen
Frequent Advisor

Re: What happens when a rollback segment gets "FULL"

Thanks, but nothing of this answers my question on what happens with the job using the rollback segment when running out of space. Will it rollback automatically or will it be placed on "hold"? In case of "placed on hold", when will it continue?
Piergiacomo Perini
Trusted Contributor
Solution

Re: What happens when a rollback segment gets "FULL"

Hi Morten K,

job will rollback automatically.

reagards
pg
Yogeeraj_1
Honored Contributor

Re: What happens when a rollback segment gets "FULL"

Hi Morten,

The error message associated with rollback segment problems is usually ORA-1555.

Suppose you are getting it against a table with 2 million records. You have other transactions modifying that table while you are reading it. These transactions are modifying and commiting their changes. Assume that at the START of your run, some process updates the "last row" in the 2 million row table and COMMITs. Their rollback entry is free to be reused now if that rollback segment they put their UNDO in wraps around. You go about processing the table and eventually get to that last row. You need to do a consistent read on it -- you need their UNDO. Unfortunately, their UNDO went into a small rollback segment that wrapped a couple of times already. The UNDO you need is long gone, you get the ORA-1555!

The error message would immediately imply a rollback of the transactions.

For you, what you need to do, is to ensure that you have sufficient rollback pre-allocated in ALL rollback segments so as these rollback segments do NOT get overwritten during the course of your longest process.

Since you are running 9i, this might be a little easier to resolve since you can set an undo rentention period (however, you must be prepared to have the disk available to support it!)

hope this helps!
kind rgards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Morten Kristiansen
Frequent Advisor

Re: What happens when a rollback segment gets "FULL"

Case solved and I got the answer that I was looking for.

ORA-01555 has nothing to do with my problem.