Re: PL/SQL error while refreshing snapshots -- please help

Your Name Here wrote:

> I get output like the following:
>
> ----------------------------------------------------------------------------
> --------
> ERDTEST: LFB5 REFRESHED SUCCESSFULLY!
> ERDTEST: T005S REFRESHED SUCCESSFULLY!
> ERDTEST: T005U REFRESHED SUCCESSFULLY!
> ERDTEST: T006 REFRESHED SUCCESSFULLY!
> ERDTEST: T006D REFRESHED SUCCESSFULLY!
> ERDTEST: SKB1 REFRESHED SUCCESSFULLY!
> begin daily_snapshot_refresh; end;
>
> *
> ERROR at line 1:
> ORA-01555: snapshot too old: rollback segment number 3 with name "RB2" too
> small
> ORA-06512: at "ERDVIEW.DAILY_SNAPSHOT_REFRESH", line 22 ORA-06512: at line
> 1
> ----------------------------------------------------------------------------
> ----
>
> My question is two parts:
> A. Why is this error not handled by the PL/SQL procedure?
> B. How can I avoid this error?
>
> Thanks,
> Chad Dunnegan

For A it seems that you don't use an exception handler. But even so your problem has nothing to do with the procedure itself!

Oracle tries to have a consistent image for data when you select data. To do it, Oracle uses
information held in rollback segments by rolling back virtually all necessary transactions that began
after the statement. Transactions entries are not cleaned as soon as they terminate. But if new
transactions reclame space, entries of old ones in rollback segments would be reused leaving an
older long time query with no possibility to rebuild a consistent image. That is exactly the meaning
of the message "Too old snapshot".

This mecanism is also expnaded to a whole transaction when you use for example a read consistent
transaction.

Then, you can overcome your problem by increasing the size of your rollback segments or "if possible"
refreshing when activity on your data base is reduced. Received on Fri Sep 04 1998 - 08:30:56 CDT