OraFAQ Forum: SQL & PL/SQL » snapshot too old

Home » SQL & PL/SQL » SQL & PL/SQL » snapshot too old (PLSQL 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

snapshot too old [message #662769]

Thu, 11 May 2017 19:32

kmnainani
Messages: 6
Registered: May 2017

Junior Member

I have the following code where the cursor returns 6985604 rows. I am using the following PL/SQL procedure to insert and delete rows.

However, although I am using BULK COLLECT, it still takes over 12 hours to come back and eventually returns "SNAPSHOT TOO OLD" ! Is there something wrong with the PL/SQL below :

DECLARE
 type xml_tbl is table of xmltype; 
  a_AUDITID DBMS_SQL.NUMBER_TABLE ;
  a_STRUCTID DBMS_SQL.NUMBER_TABLE ;
  a_OPERATION DBMS_SQL.VARCHAR2_TABLE ;
  a_PRIMARYKEY DBMS_SQL.VARCHAR2_TABLE ;
  a_USERID DBMS_SQL.NUMBER_TABLE ;
  a_TIMESTAMP DBMS_SQL.DATE_TABLE ;
  a_APPSIG DBMS_SQL.VARCHAR2_TABLE ;
  a_DATA DBMS_SQL.CLOB_TABLE ;
  a_ROWVERSION DBMS_SQL.VARCHAR2_TABLE ;
    a_ROWID DBMS_SQL.VARCHAR2_TABLE ;
  a_PARTDATE DBMS_SQL.DATE_TABLE ;
-- 
  g_ToDate   DATE := TO_DATE('18082016','DDMMYYYY') ; 
-- 
  l_rows number(9) := 0;
-- 
  CURSOR c_DMLAUDIT IS
  SELECT a.*,a.rowid, a.TIMESTAMP
  FROM DMLAUDIT a
  WHERE   a.TIMESTAMP < to_date('18082016','DDMMYYYY');

BEGIN
-- 
  dbms_output.enable  ;
  dbms_output.put_line ( 'Archiving Table : DMLAUDIT') ;
  dbms_output.put_line ( 'Started         : ' || to_char(sysdate,'HH24:MI:SS DD-MON-YYYY') ) ;
-- 
  OPEN c_DMLAUDIT;
  LOOP
    FETCH c_DMLAUDIT BULK COLLECT INTO 
        a_AUDITID,
        a_STRUCTID,
        a_OPERATION,
        a_PRIMARYKEY,
        a_USERID,
        a_TIMESTAMP,
        a_APPSIG,
        a_DATA,
        a_ROWVERSION,
         a_ROWID,
        a_PARTDATE
          LIMIT 500;
-- 
    FOR i IN 1..a_AUDITID.COUNT LOOP
      INSERT INTO ARCH_DMLAUDIT
             (
          AUDITID
         ,STRUCTID
         ,OPERATION
         ,PRIMARYKEY
         ,USERID
         ,TIMESTAMP
         ,APPSIG
         ,DATA
         ,ROWVERSION
         ,ARCH_TIMESTAMP
             )
      VALUES (
        a_AUDITID(i), 
        a_STRUCTID(i), 
        a_OPERATION(i), 
        a_PRIMARYKEY(i), 
        a_USERID(i), 
        a_TIMESTAMP(i), 
        a_APPSIG(i), 
        a_DATA(i), 
        a_ROWVERSION(i), 
        a_PARTDATE(i) ) ;
        
    
     
    END LOOP ;
-- 
BEGIN
    FORALL i IN 1..a_AUDITID.COUNT
      DELETE FROM DMLAUDIT
      WHERE audited = a_auditid(i) ;
      exception
          when others then
              if sqlcode= -24381 then
               for indx in 1..SQL%BULK_EXCEPTIONS.COUNT loop
               dbms_output.put_line ( SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX || '- ORA-' || SQL%BULK_EXCEPTIONS(indx).ERROR_CODE );
              End loop;
           end if;
   END;
-- 
  COMMIT ;
-- 
     EXIT WHEN c_DMLAUDIT%NOTFOUND ;
  END LOOP ;
  CLOSE c_DMLAUDIT ;

END ;
/

The DELETE part is taking the longest and running it outside for just 1000 rows using a simple Cursor FOR LOOP also takes 1 hour

[b]Is the error due to the COMMIT being in the Wrong place ?
Why does DELETING only a 1000 rows take that long ?

[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 11 May 2017 20:23] by Moderator