Am I loosing the plot .. but we have a select statement run from discoverer which is causing the famous snapshot too old error.
My understanding is that undo is generated from select/insert/update.
SELECT ( ROUND(( TO_DATE(SYSDATE)-o101038.HIRE_DATE )/365,2) ) as
" bla bla
ORDER BY o101020.SUB_ORGANIZATION_NAME ASC
Thanks in Advance
The SELECT statement is the victim.
Some session is doing DML against the same table against which the SELECT occurs & is likely doing COMMIT inside a LOOP.
General，ORA-01555 caused by select statement .and your sql statement Query Duration is 3866 sec
first， tuning your sql statement；
second，adjust your parameter undo_retention and the size of undo tablespace。
ORA-01555 is not CAUSED by SELECT.
SELECT is the victim; not the culprit.
The culprit or cause is due to DML against same table as the SELECT.
Hi have found the issue is due to "select causing redo due to BLOCK CLEANOUT"
this link explains it well
thanks fr all your comments
This error comes because of only one issue and that is that needed undo is not available in undo tablespace. Now, undo tablespace is used all the connections of current instance.
Undo data availability is dependent on two points. undo retention and undo tablespace space availability. I would suggest please run following query during this statement and check who is using undo.
select start_time, username, r.name,
ubafil, ubablk, t.status, (used_ublk*p.value)/1024 blk, used_urec
from v$transaction t, v$rollname r, v$session s, v$parameter p
order by 1;
note : I got this script from metalink.
It is not necessary that your query is only query which is consuming lots of undo and might possible that undo is not available for your query.
Hope it would help.
thankyou aware of the causes of snapshot and undo retention etc etc and how to resolve the issue.. Going back to the original question basically I was unaware that a select statement can cause redo. And subsequently if the undo is not available then will get snapshot error. My confusion was why does the select get this error. I now know ..