An Oracle stored procedure suddenly throws ORA-01555 while executing.
select
a,b
from table1 S into a_var,b_var
where s.abc=systedate
and requiedate between add_months(sysdate,-2) and sysdate
AND Currency= NVL(currency_CODe,USD)
group by S.actcount;
table1_invoice(1)=a_var;
table1_invoice(2)=b_var;
FORALL indx in 1..test.count SAVE EXCEPTIONS
insert into table2 values
table1_invoice(indx);
When the procedure was running and using table A, I executed an index re-build in parallel on the same table.
Once that completed, I executed gather stats on table A.
Does this things create error ORA-01555? Does rebuild index consume a rollback segment and the old snapshot of uncommitted data is removed?
I have pasted dummy code.
I execute index re-build in parallel on the same table.
This is your likely cause. ORA-1555 pertains to being able to give you a consistent view of the data. For example, using your dummy code as a template:
So where does ORA-1555 fit in? What if our query ran for (say) an hour? Now we might need to be undo-ing other transactions that ran nearly an hour ago. There is only so much space we reserve for the undo for (completed) transactions, because we need to free it up for new transactions as they come in. We throw away the old stuff because those transactions have committed. So revisiting the processing above, the following might happen:
Now we're stuck, because we cannot give you the data as it was at 9am anymore because we can't take some changed data all the way back to 9am. The snapshot in time of the data you want is too old.
Hence "ORA-1555: Snapshot too old"
This is why the most common solution is just to retry your operation because now you are starting your query at a more recent time.
So you can see - the more activity going on against the database from OTHER sessions at the time of your query, the greater the risk of hitting a ORA-1555 because undo space is being consumed quickly and thus we might throw away the older stuff more rapidly.