Undo and snapshort too old

How do you fix snapshot too old when using UNDO's in 9.2.0

ORA-01555: snapshot too old: rollback segment number 10 with name "_SYSSMU10$"
too small

You can extend the datafile for your undo tablespace.
What SQL were you running when you got the error message?
Normally when someone is performing a long running query
on a table that is changing, eventually Oracle not be able to
maintain a read consistant view of the underlying table.

If you change your code you might also be able to eliminate the
snapshot too old error message.

First of all, you need to identify the longes running query.
Second, you need to set the undo_retention initialization parameter to that time (say your longest running query is 2 hours, set undo_retention=7200).
Third, allocate enough space un the undo tablespace.

Cheers,

Quote:

---

Originally posted by gandolf989

If you change your code you might also be able to eliminate the
snapshot too old error message.

---

Like having a commit every # of records.

Quote:

---

Originally posted by reydp
Like having a commit every # of records.

---

that's worse

Another classic reason for ORA-01555 is to leave cursors opened and fetch across commits. Depending on the query, it doesn't even need to be so "long-running"

Quote:

---

Originally posted by adrianomp
Another classic reason for ORA-01555 is to leave cursors opened and fetch across commits. Depending on the query, it doesn't even need to be so "long-running"

---

Of course most cursors should be implicit cursors, removing the necessity to close cursors in code, as they will be closed for you. But certainly, fetching accross commits with open cursors will cause the ORA-01555.