ORA-01555 Snapshot too old error on 10g

Hi all,

I have been looking at this new database we have and seen alot of ORA-01555's in the alert log (started happening today). The database is 10.1.0.3 on Redhat Linux.

The undo_retention period is 4500

And there is ample space available for the undo tbs to extend.

The alert log points the query has been running for 1186764763s!! The query is trying to insert images into a table but surely one cannot increase the undo_retention time to match the length of query run? Is there anything that can be done to get rid of the error (i know tuning the query is one) but any others would be appreciated!

Thanks in advance,
Chucks

Shorten the undo retention, use temporary tables so that you can break up the query into smaller chunks.

Is the query an insert into.. select * from x? The 1555 should be from what you're selecting from, rather than the target of the insert.

By the way Gandolf, I love that signature...

Hi,

I actually increased the undo_retention to 4500, it was set to 900 orginally. Do u mean decrease the the undo in conjunction with the use to temp tables? Because i thought the solution to getting rid of ORA-01555 errors is to increase the undo_retention parameter.
Also this database is shutdown daily so i cannot understand how the query has been running for such a long time?

Thanks in advance

Hi,

Just for info this is the query:

insert into x (rawData, blob, captureDate, captureKey, vrm, IntelHandlingCodeTypeId, IntelIntelligenceGradeTypeId, IntelSourceGradeTypeId, GPMSMarkingId, weedDate, deleted, urn) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)

As can be seen its a normal insert but with rawdara (LONG RAW) and a blob.

Thanks

There are several articles on Metalink on LOBs, seems the rollback for LOBs is stored with the LOB (which makes sense).

Check these out:

452341.1
66431.1
162345.1