Ora-01555 : Snapshot too old error

By this time you might have come accross this ora-01555 error lot of times. Before learning about the causes of the Ora-01555 error, you need to understand about Read Consistency and dirty reads in Oracle.

Read Consistency : Let us take an example of Reading a 1 million records from a table emp through a cursor.
While fetching the data from the cursor in session A, if another session B updates the data simultaneously in the table emp by which 10000 records has been affected in the set of 1 million records. if these 10000 rows are not yet read by the cursor in session A, then

 Whether the Oracle would read the updated records to Session A ? NO.

 
Session A has started before the session B has started. Read consistency is the feature of Oracle that gurantees you the Snapshot of data available to you at the start of the transaction. Oracle would return those old 10000 rows to session A which were available before the start of session B.

 
Oracle does this by the use of Roll Back segments. Oracle constructs the Original data by using the data available in Roll back segments.

Lets us see the Process of Oracle updating the data:-

A data block Consists of a data block header and its data storage. similarly a rollback segment consists of a rollback segment header and a roll back segment data storage. A data block header stores the reference of the rollback segment which caused its state to change. After commiting the change to the data, the data block header is not updated but the corresponding entry in the rollback segment header is updated. Now when the changed data block is revisited by another transaction, the data block header is updated. This process is called delayed cleanout.

Stage 1: A normal view of data block and a rollback segment header. Data block header shows no reference to any transaction.

Stage 2: with an update statement , existing row 2 data will be copied to rollback sement. Row 2 data will be updated by the New values coming from the transaction 2. Data block header records a reference to transaction 2 and holds the status of the data block as uncommitted. In the Rollback segment the transaction 2 status would be changed to UNCOMMITTED.

Stage 3 : if commit is applied for transaction 2, corresponding changes are made to data Roll back segment header, but not to the data block.

Stage 4 : if another query requests for the rows in the data block, Oracle updates the data block header status with no reference to any transaction and its status. This is called as delayed block clean out.

Solutions for the ora-01555 error :
1. Increase the Size of Roll back segment by adding more space to Roll back segment.

 
2. Add multiple roll back segments .
An example of adding a rollback segment

CREATE PUBLIC ROLLBACK SEGMENT rbs_02
TABLESPACE rbspace
STORAGE (
INITIAL 120K
NEXT 120K
OPTIMAL 4M
MINEXTENTS 20
MAXEXTENTS 100 );

When a rollback segment is created it is not used by Oracle , unless the newly created rollback segment is made online. You can make a roll back segment online or offline by using ALTER ROLLBACK SEGMENT

ALTER ROLLBACK SEGMENT rbs_02 ONLINE;

3. Reducing the frequency of commits in Pl/sql subprogram. Executing the commit command after each INSERT/UPDATE/DELETE would generate lot of Undo SPACE.
Try to execute the commit command for a bunch of 5000 records or so

if loop_count = 500 then
commit;
end if;

3. Run the processing against a range of data.

4.Fetching accross commits should be avoided at any cost.Don’t fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.

 
5.Ensure that the outer select does not revisit the same block at different times during the processing.

6. shfit to Oracle 9i automatic undo management to minimize the Ora-01555 : Snapshot too old errors to a greater extent.