ora-1555 error ,query duration less then undo retention duration
i am getting this error in my alert log .
ORA-01555 caused by SQL statement below (SQL ID: 2zy0xyvkt00h0, Query Duration=13090 sec, SCN: 0x0005.8559d9ce):
Thu Feb 3 04:48:09 2011
SELECT "MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO" , "TIMESTMP" , "PAGELG" , "VARDATA" FROM "RFBLG" WHERE "MANDT" = :A0 AND "BUKRS" = :A1 AND ( "BELNR" > :A2 OR "BELNR" = :A3 AND "GJAHR" > :A4 OR "BELNR" = :A5 AND "GJAHR" = :A6 ) ORDER BY "MANDT" , "BUKRS" , "BELNR" , "GJAHR" , "PAGENO"
my undo retention period is :
undo_management string AUTO
undo_retention integer 21600
as the duration taken by sql query is less then undo retention period ,
and my undo tablespace size is : UNDO 10,240.00 MB
what can i do to avoid this problem .
thanks in advance
Most probably a feature than a problem
What's your "undo retention guarantee" setting?
If not enabled Oracle will reduce undo_retention as needed if running out of undo space then queries will fail with ORA-01555 even if running for less time than the one originally set on unde_retention.
If you enable "undo retention guarantee" closely monitor undo space - more likely you will have to extend undo tablespace to cope with the extra load.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width