What happens when a rollback segment gets "FULL"

[Morten Kristiansen]

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

[Peter Godron]

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]

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 · Accepted solution]

Hi Morten K,

job will rollback automatically.

reagards
pg

[Yogeeraj_1]

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

[Morten Kristiansen]

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

ORA-01555 has nothing to do with my problem.