Problem Description:
====================
You receive the following error:
ORA-01562: “failed to extend rollback segment number %s”
Cause: Failure occurred when trying to extend rollback segment
Action: This is normally followed by another error message that caused
the failure. You may take the rollback segment offline to
perform maintainence. Use the alter rollback segment offline
command to take the rollback segment offline.
You check the “v$rollstat” view to find the status of rollback segment is
listed as FULL. What does this mean?
Solution Description:
=====================
A rollback segment is marked as FULL when an error ORA-1562 is signalled.
When the rollback segment is marked with a status of FULL, no new transactions
will use the rollback segment. The FULL status is intended to signal
transactions to use other valid rollback segments. All transactions in
that rollback segment must complete before the full status is cleared. This
means that all transactions must either be committed or rolled back.
If it is not possible to commit every transaction, possible workarounds are:
a. Taking the rollback segment offline and bringing back online should also
clear the full status. This could potentially take awhile if there are a
large number of transaction that need to be rolled back.
Alter rollback segment
Alter rollback segment
b. Adding a datafile to the rollback segment tablespace so that the segment
can extend. You can then continue with the *current* txn (that caused the
rollback segment to get status FULL) and the status will then get reset to
ONLINE.
If you are unable to wait for the full status to be cleared, then bouncing
the database will clear the rollback segment.
Possible workaround is to use set transaction. This is supposed to override
the full flag and still use the full rollback segment.
Set transaction use rollback segment
Explanation:
============
The full status on a rollback segment is cleared when all transactions in the
rollback segment are completed (committed or rolled back) and it is determined
that the segment can wrap. It is also cleared if the number of active
transactions in the rollback segment is zero.