recently, i have been facing snapshot too old error. So, i tried to understand the why this is happening. Link to the snapshot too old article Still, i have some doubts regarding the error.

  1. If i run a select query on a table with millions of data and new data are being inserted into the table, does it cause the above error? Doesn't it change the image captured b4 running the query?

2."Something like:

10AM query starts, never ends

11AM query starts, never ends

Noon query starts, never ends

1PM query starts

Meanwhile, the undo needed from the 10AM query for the 1PM query gets overwritten, 1PM query dies with ORA-01555, since it needs to know what the table looked like before the 10AM query started mucking with it.

Also if the query is a loop with a commit in it, it can do the same thing without other queries, as eventually the next iteration requires looking back at it's own previous first generation, can't do it, and barfs."

Above statements are taken from the link given above. I don't understand the highlighted part. Does it says that if i add a commit after delete statement with in a loop, then next iteration results in an error. But, i have been doing such things for sometime and i haven't got any error.

Please help me clear these doubts.

  • See this working example that replicates highlighted part – Kumar Oct 4 at 0:08

Regarding the ORA-01555: snapshot too old errors with select statements - this usually happens when you have a long running query with data changes whilst it's running, and an undersized UNDO tablespace.

When you run a select statement Oracle makes it read-consistent, meaning that any changes to the data after you started the query won't be seen by the query. When changes to the data have performed (whether they're committed or not - this is why rolling back changes can take a long time!) the database blocks are updated, and the means to undo the change are stored in the UNDO tablespace.

When your query comes to read a row that has been changed, it now needs to go to the UNDO tablespace to work out what the old values were. If, however, that information has been overwritten due to other changes (e.g. the changes were committed and the UNDO information is no longer needed for rollback, and other changes have come along and needed the space), you will get the snapshot too old error.

Regarding committing within a cursor loop (aka "fetching across a commit"), something similar can occur. If you have an open cursor, but you commit before you come to the end of the data, you have effectively told Oracle that you no longer care about the UNDO information.

Then, the changes you're making in the loop may well overwrite blocks in the UNDO tablespace that your cursor relies on and bam! ORA-01555.

This AskTOM question has more information on ORA-01555, which I suggest you read (and also search in AskTOM for other ORA-01555 questions; there is plenty of decent information available there!).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.