Case 1: An active database with an insufficient number of small-sized rollback segments.
If your database has many transactions changing data and committing changes very often, as in an Online Transactions Processing (OLTP) environment, then the chance of reusing the space used by a committed transaction is higher. A long-running query then may not be able to reconstruct the snapshot due to wraparound and overwrite in rollback segments. Larger rollback segments in this case will reduce the chance of reusing the committed transaction slots.
In this case you should consider adding more rollback segments and increasing their size. The rollback segments' size and number depend on the demands of your application and the number of concurrent users.
Also make sure all rollback segments are online except ones that are reserved for large transactions and are intentionally kept offline. The more segments that are online, the more transactions that are spread out and the less often any individual transaction will be overwritten.
In the rollback storage clause, there is a parameter called OPTIMAL that specify the optimal rollback segment in bytes. When it is set, the database will try to keep the segment at the specified size, rounded up to the extent boundary. If additional space is needed beyond the optimal size, the rollback segment will expand beyond optimal size to accommodate the current transaction(s), but will eventually deallocate extents to shrink back to this size. When the OPTIMAL parameter is set too low, it could lead to frequent ORA-01555 errors because old transaction data may now be eliminated in two ways: by being overwritten or by being discarded during the shrinking process.
To provide best performance, set all of your rollback segments to a size where every single transaction always fits. In practical terms, this may well be impossiblefor example, if your largest transaction is 500MB and you require 30 rollback segments for concurrency.
Segments should have an optimal size large enough so that 90 percent or better of transactions will fit without having to extend the segment. In addition, the rollback tablespace should be large enough so that when all rollback segments are at the optimal value, there is plenty of space for them to extend when it becomes necessary. For example, if your segments are set with an optimal value of 50MB and you know that there is a particular transaction that runs infrequently, but requires 1GB when it does run, your rollback tablespace must have at least 950MB free (an absolute minimum) when all segments in that tablespace are at optimal size. Because you cannot normally count on either all segments to be at optimal or the big transaction to be the only one using space in the rollback segment, you should have at least 30 to 40 percent more space available than the absolute minimum.
For a batch jobs, data uploads, or any other processes that may contain larger than regular activity transactions, consider creating a large rollback segment and assigning a transaction to it, using the following syntax:
SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large;
Case 2: A rollback segment corruption that prevents a consistent read requested by the query.
If a rollback segment is corrupted and cannot be read, then a statement in the code needing to reconstruct a before-image snapshot will result in the ORA-01555 error. In this case I recommend that you drop and recreate the rollback segment while the database is up and running.
You can drop a rollback segment only if it is offline. To determine whether a rollback segment is offline, query the data dictionary view called DBA_ROLLBACK_SEGS. Offline rollback segments have a STATUS value of AVAILABLE, while offline rollback segments have a STATUS value of IN_USE.
Let's say corruption occurred in rollback segment RBS_02. To put this rollback segment offline and then drop it, execute the following two statements:
ALTER ROLLBACK SEGMENT RBS_02 OFFLINE;
DROP ROLLBACK SEGMENT RBS_02;
When you create a rollback segment, it is initially offline and you have to bring it online to make it available for transactions by your Oracle instance. The following statements create a rollback segment RBS_02 with default storage values in the RBS_DATA tablespace and bring it online:
CREATE ROLLBACK SEGMENT RBS_02 TABLESPACE RBS_DATA;
ALTER ROLLBACK SEGMENT RBS_02 ONLINE;
You must have ALTER ROLLBACK SEGMENT, DROP ROLLBACK SEGMENT, and CREATE ROLLBACK SEGMENT system privileges to do this. Also, you have to include the TABLESPACE clause in the CREATE ROLLBACK SEGMENT command or else Oracle will create the rollback segment RBS_02 in the SYSTEM tablespace, which Oracle doesn't recommended.
These two cases show how an Oracle DBA can approach the ORA-01555 error problem. The third case, where you fetch across commits while your cursor is open, is covered in my follow-up 10-Minute Solution.