ORA-01555 Snapshot Too Old:rollback segment number with name "" too small-CodePudding

Oracle store 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 execute 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 rollback segement the old snapshot of uncommited data is removed.

I have pasted dummy code.

CodePudding user response：

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:

You open your cursor at 9am
You start fetching from that cursor at 9am and lets say the total execution of the query takes 60 seconds.
So lets say you at the 40 second mark of that fetch. Because (you) reading data does not block others from changing it, you might come across some data that has been recently changed (say 3 seconds ago) by someone else.
We can't give you THAT data, because we have to show you the data as it was at 9am, (when your query started).
So we find the transaction(s) that changed that data 3 seconds ago, and use the undo information those transactions wrote to reverse out the changes. We'll continue to do that until the data now looks like it did at 9am
Now we can use that (undone) data because it is consistent with the time you opened the cursor.

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:

You open your cursor at 9am
You start fetching from that cursor at 9am and lets say the total execution of the query takes 60 seconds.
So lets say you at the 40 second mark of that fetch. Because (you) reading data does not block others from changing it, you might come across some data that has been recently changed (say 3 seconds ago) by someone else.
We can't give you THAT data, because we have to show you the data as it was at 9am, (when your query started).
So we find the transaction(s) that changed that data 3 seconds ago and use the undo information those transactions wrote to reverse out the changes.
OH NO! That undo information has been discarded!!!

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.