Re: -1555-ORA-01555: snapshot too old: rollback segment number 7 with name "_SYSSMU7$" too small

From: <spremuta_at_gmail.com>
Date: 12 Sep 2006 06:41:30 -0700
Message-ID: <1158068490.517186.270630@e63g2000cwd.googlegroups.com>

DA Morgan ha scritto:

> Post the PL/SQL that is causing the 1555.
> --
> Daniel Morgan

this should be the code...

 DECLARE
/*-------------------------------------------------------------------------------*/
 /*-- dichiarazione variabili

          */

/*-------------------------------------------------------------------------------*/

     WORK_R_POLO
NETFLOW_IUM_EXT_H_DESTINATION.POLO%TYPE;
     WORK_R_DESTINATION_ADDRESS
NETFLOW_IUM_EXT_H_DESTINATION.DESTINATION_ADDRESS%TYPE;
     WORK_R_PROTOCOL
NETFLOW_IUM_EXT_H_DESTINATION.PROTOCOL%TYPE;
     WORK_R_START_TIME
NETFLOW_IUM_EXT_H_DESTINATION.START_TIME%TYPE;
     WORK_R_BYTES
NETFLOW_IUM_EXT_H_DESTINATION.BYTES%TYPE;
     WORK_R_PACKETS
NETFLOW_IUM_EXT_H_DESTINATION.PACKETS%TYPE;

     CONTATORE_COMMIT                  INTEGER(06) := 0;
     FREQUENZA_COMMIT                  INTEGER(06) := 250000;



/*-------------------------------------------------------------------------------*/

 /*-- dichiarazione cursori
          */



/*-------------------------------------------------------------------------------*/

       CURSOR RNETFLOW_IUM_CUR1_DESTINATION IS
         SELECT
         POLO,
         DESTINATION_ADDRESS,
         PROTOCOL,
         TRUNC(START_TIME,'dd'),
         SUM(BYTES),
         SUM(PACKETS)
       FROM NETFLOW_IUM_EXT_H_DESTINATION
       WHERE TRUNC(START_TIME,'dd') = TRUNC(SYSDATE-1,'dd')
       GROUP BY

POLO,DESTINATION_ADDRESS,PROTOCOL,TRUNC(START_TIME,'dd');

 BEGIN
/*-------------------------------------------------------------------------------*/
 /*-- loop di lettura tabella tramite cursore

          */

/*-------------------------------------------------------------------------------*/

       OPEN RNETFLOW_IUM_CUR1_DESTINATION;
       LOOP
               FETCH RNETFLOW_IUM_CUR1_DESTINATION
               INTO
                 WORK_R_POLO,
                 WORK_R_DESTINATION_ADDRESS,
                 WORK_R_PROTOCOL,
                 WORK_R_START_TIME,
                 WORK_R_BYTES,
                 WORK_R_PACKETS;
               EXIT WHEN RNETFLOW_IUM_CUR1_DESTINATION%NOTFOUND;



/*-------------------------------------------------------------------------------*/

 /*-- insert NETFLOW_IUM_EXT_D_DESTINATION
                          */



/*-------------------------------------------------------------------------------*/

               INSERT INTO
               NETFLOW_IUM_EXT_D_DESTINATION
               (POLO,
                DESTINATION_ADDRESS,
                PROTOCOL,
                START_TIME,
                BYTES,
                PACKETS
               )
               VALUES
               (WORK_R_POLO,
                WORK_R_DESTINATION_ADDRESS,
                WORK_R_PROTOCOL,
                WORK_R_START_TIME,
                WORK_R_BYTES,
                WORK_R_PACKETS
               );
               IF CONTATORE_COMMIT = FREQUENZA_COMMIT
                 THEN CONTATORE_COMMIT := 1;
                 COMMIT;
               END IF;
       END LOOP;
       CLOSE RNETFLOW_IUM_CUR1_DESTINATION;
       COMMIT;

INSERT_NETFLOW_IUM_LOG('INSERT_NETFLOW_IUM_DESTINATION_D.SQL','OK','ELABORAZIONE OK');

 EXCEPTION
       WHEN OTHERS THEN INSERT_NETFLOW_IUM_LOG('INSERT_NETFLOW_IUM_DESTINATION_d.SQL','KO',SQLCODE||'-'||SUBSTR(SQLERRM, 1, 100));
 END; Received on Tue Sep 12 2006 - 08:41:30 CDT