Re: Rollback segment error

In article <s5jo34alqrs184_at_corp.supernews.com>,   "Ari Kaplan" <akaplan_at_interaccess.com> wrote:
> The problem is that your rollback segments are too small. The
> ORA-1555 really has to do with rollback segments ; "snapshot"
> is a misnomer. Increase the NEXT size for your rollback segments.

Hi Ari, how's it going? Congratulations on the cover article in "Contract Professional".

All -- please excuse the length of this post. It's difficult to explain the causes of ORA-01555 briefly!

In point of fact, though, there are four possible causes for ORA-01555. Some of them might be helped by increasing the size of the rollback segment; some will /not/.

INACTIVE IN-USE DATA The most common cause of ORA-01555 is that you are performing a long-running query in which at least one of the base tables is being updated concurrently by other processes. This is a problem because it can lead to INACTIVE, IN-USE DATA in the rollback segment.

Here's the deal. When you have a long-running query, it maintains a read-consistent view of the database as of the beginning of the query. Whenever the query gets to a block which has been modified by another process, it reconstructs the before-change view of the data by reading the rollback information for the transaction which caused the change. This is what we mean when we say that the rollback data is "in-use".

However, if the transaction which caused the change has been committed, the rollback information for the transaction is considered "inactive". That means that other transactions which are using that rollback segment can roll around to the extent(s) containing the in-use data, overwriting them.

Hence, when the long-running query attempts to read one of the changed blocks, it tries to look up the rollback information associated with the transaction, finds that it has been overwritten, and throws the ORA-01555 error.

The possible remedies for the situation are:

• Ensure that the base tables for your long queries are not being updated while the queries are taking place;
• Increase the size of /all rollback segments/ so that database activity will possibly not be overwitten;
• Simply try again in the hopes that there won't be too much database modification activity.

FETCHES ACROSS COMMITS If you have a process -- such as a PL/SQL stored procedure -- which opens a cursor, fetches data from it, performs database operations, commits those changes, and continues fetching from the initial cursor, this can also cause ORA-01555 to occur. There are two distinct ways in which fetching across commits can cause ORA-01555, and I don't feel like explaining them. My advice is: do not do this. As soon as you commit a transaction, assume that all of your cursors are invalid, and close them.

CORRUPT ROLLBACK SEGMENT This is the least common cause of ORA-01555, and is the easiest to address. Simply drop and recreate the rollback segment.

Hope this helps.

Regards,

-bn

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 17 1999 - 13:28:31 CST