ORA-01555 with Query Duration=0 sec

Most DBAs know that ORA-1555 is caused by long running query. And in alert.log file it will tell you which SQL caused ORA-1555 and run for how long.
However from time to time you will see errors like this. It's basically tell you that the query failed right away. So why's the case?

Mon Aug 29 06:39:09 2011

ORA-01555 caused by SQL statement below (SQL ID: 0jc2g6km899ps, Query Duration=0 sec, SCN: 0x00ae.75483a06):

Mon Aug 29 06:39:09 2011

SELECT.xxxx

I ran  a query to find out the time stamp of this query's SCN and found out that the query has a time stamp of 6AM. But i was failed 40 min later. That could only mean one thing that it was in a transaction that started 6AM and Oracle already over written the data in UNDO.

SYS@VAULTPROD>select scn_to_timestamp(749291977222) from dual;

SCN_TO_TIMESTAMP(749291977222)

---------------------------------------------------------------------------

29-AUG-11 06.00.01.000000000 AM

There’s a couple of ways to help improve the situation.

• Does all the statements in this job need to be in single transaction? If not, don’t put them into single transaction.
• Increase undo retention of DB, Oracle will try to honor this retention subject to UNDO space.
• Increase the UNDO tablespace to mitigate the potential space squeeze but remember the reason we got this error is not from UNDO space limitation.