PL/SQL error while refreshing snapshots -- please help

From: Your Name Here <yourname_at_monsanto.com>
Date: 29 Aug 1998 19:48:52 GMT
Message-ID: <01bdd386$2d10dc40$9f0c850a@cadunn.monsanto.com>

When I call the following PL/SQL procedure:

CREATE OR REPLACE procedure daily_snapshot_refresh as begin
declare

CURSOR C_USER_SNAPSHOTS IS

       SELECT NAME FROM USER_SNAPSHOTS
       WHERE LAST_REFRESH<SYSDATE - .1;

V_SNAPSHOT_NAME USER_SNAPSHOTS.NAME%TYPE; begin

FOR V_NAME IN C_USER_SNAPSHOTS LOOP

      V_SNAPSHOT_NAME := V_NAME.NAME;
      begin
        dbms_snapshot.refresh(V_SNAPSHOT_NAME);
        DBMS_OUTPUT.PUT_LINE('ERDTEST:  '||V_SNAPSHOT_NAME||' REFRESHED
SUCCESSFULLY!');
        DBMS_OUTPUT.PUT_LINE(' ');
        commit;
      exception
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERDTEST:  '||V_SNAPSHOT_NAME||' NOT
REFRESHED!');
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
        DBMS_OUTPUT.PUT_LINE(' ');
        commit;
      end;

END LOOP;
end;
end;

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:

Why is this error not handled by the PL/SQL procedure?
How can I avoid this error?

Thanks,
Chad Dunnegan

--
Any comments or statements made are not necessarily those of Monsanto Corporation, its subsidaries or affiliates. Received on Sat Aug 29 1998 - 14:48:52 CDT