sqloracle-databaseplsqluser-defined-typesbulk-operations

Moving huge number of data in chunks


I am working on a script where a huge number of data should be queried based on some conditions and moved to some archive tables respectively. I have over fifty millions of records to scan through and select the matching records in order to perform INSERT operations for six archive tables. The below script works fine for about half a million of records but throws the below exception when running for millions of records

Error report:
ORA-04036: PGA memory used by the instance exceeds PGA_ AGGREGATE _LIMIT

Other than increasing the PGA_ AGGREGATE _LIMIT I want to improve my script in a way that it avoids loading all the records into memory but runs the script and insert values to tables in chunks. Currently I have no idea on how this should be done. Can someone suggest me to avoid memory insuffiicient issues by letting the script run in batch wise

below is part of my script (inserting values into four tables are shown).

CREATE OR REPLACE TYPE R1_ID_TYPE IS TABLE OF NUMBER;
/
CREATE OR REPLACE TYPE R5_ID_TYPE IS TABLE OF NUMBER;
/
DECLARE
     R01_IDS R1_ID_TYPE;
     R05_IDS R5_ID_TYPE;

BEGIN
    --add the R05_IDs which are older than five years from R5_TABLE and R6_TABLE to R5_ID_TYPE nested table
    SELECT  R5.R05_ID AS R05_ID
    BULK COLLECT INTO R05_IDS
    FROM R6_TABLE R6 , R5_TABLE R5
          WHERE R5.R05_ID = R6.R06_R05_ID_FK
          AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
          AND R5.R05_STATUS = 'D'
          AND R6.R06_STATUS = 'D';

    -- Inserts all the deregistered records which are older than five years from R5_TABLE and R6_TABLE tables to the relevant archive tables
    INSERT ALL
    INTO R5_TABLE_archived(
      R05_ID, 
      R05_R01_ID_FK,
      R05_NUMBER,
      R05_NUMBER_TYPE,
      R05_STATUS,
      R05_GSM_SUBSCRIBER_TYPE
      R05_DATE_TIME_CAPTURED) 
      values (
        R5_R05_ID, 
        R5_R05_R01_ID_FK,
        R5_NUMBER,
        R5_NUMBER_TYPE,
        R5_R05_STATUS,
        R5_R05_GSM_SUBSCRIBER_TYPE,
        R5_R05_DATE_TIME_CAPTURED)
    INTO R6_TABLE_archived(
        R06_ID,
        R06_R05_ID_FK,
        R06_R08_ID_FK,
        R06_STATUS,
        R06_REFERENCE_NUMBER,
        R06_DATE_TIME_CAPTURED,
        R06_DATE_EXPIRED) 
        values (
        R6_R06_ID,
        R6_R06_R05_ID_FK,
        R6_R06_R08_ID_FK,
        R6_R06_STATUS,
        R6_R06_REFERENCE_NUMBER,
        R6_R06_DATE_TIME_CAPTURED,
        R6_R06_DATE_EXPIRED)   
    SELECT R5_R05_ID, 
        R5_R05_R01_ID_FK,
        R5_NUMBER,
        R5_NUMBER_TYPE,
        R5_R05_STATUS,
        R5_R05_GSM_SUBSCRIBER_TYPE,
        R5_R05_DATE_TIME_CAPTURED,
        R6_R06_ID,
        R6_R06_R05_ID_FK,
        R6_R06_R08_ID_FK,
        R6_R06_CHANGE_SOURCE,
        R6_R06_REFERENCE_NUMBER,
        R6_R06_DATE_TIME_CAPTURED,
        R6_R06_DATE_EXPIRED
    FROM
    (
    SELECT R5.R05_ID R5_R05_ID, 
        R5.R05_R01_ID_FK R5_R05_R01_ID_FK,
        R5.R05_NUMBER R5_NUMBER,
        R5.R05_NUMBER_TYPE R5_NUMBER_TYPE,
        R5.R05_STATUS R5_R05_STATUS,
        R5.R05_GSM_SUBSCRIBER_TYPE R5_R05_GSM_SUBSCRIBER_TYPE,
        R5.R05_DATE_TIME_CAPTURED R5_R05_DATE_TIME_CAPTURED,
        R6.R06_ID R6_R06_ID,
        R6.R06_R05_ID_FK R6_R06_R05_ID_FK,
        R6.R06_R08_ID_FK R6_R06_R08_ID_FK,
        R6.R06_STATUS R6_R06_STATUS,
        R6.R06_REFERENCE_NUMBER R6_R06_REFERENCE_NUMBER,
        R6.R06_DATE_TIME_CAPTURED R6_R06_DATE_TIME_CAPTURED,
        R6.R06_DATE_EXPIRED R6_R06_DATE_EXPIRED
      FROM R6_TABLE R6 , R5_TABLE R5
      WHERE R5.R05_ID = R6.R06_R05_ID_FK
      AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
      AND R5.R05_STATUS = 'D'
      AND R6.R06_STATUS = 'D');       

    --selects all the R01 IDs which matches with the above criteria and copy values to respective archive tables
    SELECT UNIQUE R1.R01_ID AS R01_ID
    BULK COLLECT INTO R01_IDS                      
    FROM R1_TABLE R1, R5_TABLE R5
    WHERE R5.R05_ID IN (Select column_value from table(R05_IDS))
    AND R1.R01_ID NOT IN (
                        SELECT R01.R01_ID
                        FROM R1_TABLE R01,
                               R5_TABLE R05
                        WHERE R05.R05_STATUS != 'D'
                               AND R01.R01_ID = R05.R05_R01_ID_FK)
    AND R1.R01_ID = R5.R05_R01_ID_FK;   

    --insert R1_TABLE tables values which matches with the above criteria into the R1_TABLE_ARCHIVED table
    INSERT ALL
    INTO R1_TABLE_ARCHIVED(R01_ID,R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)  
    VALUES (RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED)
    SELECT RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED
    FROM (
        SELECT
            r1.R01_ID  RA1_R01_ID,
            r1.R01_ID_TYPE  RA1_R01_ID_TYPE,
            r1.R01_IDENTITY_NUMBER  RA1_R01_IDENTITY_NUMBER,
            r1.R01_PASSPORT_COUNTRY  RA1_R01_PASSPORT_COUNTRY,
            r1.R01_DATE_TIME_CAPTURED  RA1_R01_DATE_TIME_CAPTURED
            FROM
            R1_TABLE r1
            WHERE 
            r1.R01_ID IN (Select column_value from table(R01_IDS))
    );

    --insert R2_TABLE tables values which matches with the above criteria into the R2_TABLE_ARCHIVED table
    INSERT ALL 
    INTO R2_TABLE_ARCHIVED(R02_ID,R02_R01_ID_FK,R02_fname,R02_SURNAME,R02_CONTACT_NUMBER,R02_DATE_TIME_CAPTURED)
    VALUES(RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED)
    SELECT  RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED
    FROM (
            SELECT
            r2.R02_ID  RA2_R02_ID,
            r2.R02_R01_ID_FK  RA2_R02_R01_ID_FK,
            r2.R02_fname  RA2_R02_fname,
            r2.R02_SURNAME  RA2_R02_SURNAME,
            r2.R02_CONTACT_NUMBER  RA2_R02_CONTACT_NUMBER,
            r2.R02_DATE_TIME_CAPTURED  RA2_R02_DATE_TIME_CAPTURED
            FROM
            R2_TABLE r2
            WHERE 
            r2.R02_R01_ID_FK IN (Select column_value from table(R01_IDS)));     


    --All the delete queries to remove the above copied values from the parent tables respectively  
    DELETE FROM R1_TABLE WHERE R01_ID IN (Select column_value from table(R01_IDS));
    DELETE FROM R2_TABLE WHERE R02_R01_ID_FK IN (Select column_value from table(R01_IDS));
    DELETE FROM R5_TABLE WHERE R05_R01_ID_FK IN (Select column_value from table(R05_IDS));
    DELETE FROM R6_TABLE WHERE R06_R05_ID_FK IN (R05_IDS);      


COMMIT;
END;
/
COMMIT;

Solution

  • Collections (and other PL/SQL constructs) are stored in session memory. (Unlike queried data which is stored in Global memory). Because session memory is allocated on a per user basis there has to be a limit, because RAM is still a relatively expensive resource.

    So, you are getting this error ...

    ORA-04036: PGA memory used by the instance exceeds PGA_ AGGREGATE _LIMIT
    

    ... because your session has hoovered up all the memory allocated the PGA (the pool of memory available to sessions).

    The problem is you are attempting to populate a collection with millions of rows. Even though that row is very narrow that's still not on. Fortunately PL/SQL has a solution: it's the LIMIT clause.

    With LIMIT we can populate a collection with a chunk of a result set, process it and get the next chunk. There's not much to change:

    DECLARE
         R01_IDS R1_ID_TYPE;
         R05_IDS R5_ID_TYPE;
         cursor r5_cur is
            SELECT  R5.R05_ID
            BULK COLLECT INTO R05_IDS
            FROM R6_TABLE R6 , R5_TABLE R5
              WHERE R5.R05_ID = R6.R06_R05_ID_FK
              AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
              AND R5.R05_STATUS = 'D'
              AND R6.R06_STATUS = 'D';       
    BEGIN
        -- this is new
        open r5_cur;
        loop
            fetch r5_cur 
            BULK COLLECT INTO R05_IDS limit 100000;
            exit when R05_IDS.count() = 0;
    
            -- this is all your code
    
            -- Inserts all the deregistered records which are older than five years from R5_TABLE and R6_TABLE tables to the relevant archive tables
            INSERT ALL
            INTO R5_TABLE_archived(
              R05_ID, 
              R05_R01_ID_FK,
              R05_NUMBER,
              R05_NUMBER_TYPE,
              R05_STATUS,
              R05_GSM_SUBSCRIBER_TYPE
              R05_DATE_TIME_CAPTURED) 
              values (
                R5_R05_ID, 
                R5_R05_R01_ID_FK,
                R5_NUMBER,
                R5_NUMBER_TYPE,
                R5_R05_STATUS,
                R5_R05_GSM_SUBSCRIBER_TYPE,
                R5_R05_DATE_TIME_CAPTURED)
            INTO R6_TABLE_archived(
                R06_ID,
                R06_R05_ID_FK,
                R06_R08_ID_FK,
                R06_STATUS,
                R06_REFERENCE_NUMBER,
                R06_DATE_TIME_CAPTURED,
                R06_DATE_EXPIRED) 
                values (
                R6_R06_ID,
                R6_R06_R05_ID_FK,
                R6_R06_R08_ID_FK,
                R6_R06_STATUS,
                R6_R06_REFERENCE_NUMBER,
                R6_R06_DATE_TIME_CAPTURED,
                R6_R06_DATE_EXPIRED)   
            SELECT R5_R05_ID, 
                R5_R05_R01_ID_FK,
                R5_NUMBER,
                R5_NUMBER_TYPE,
                R5_R05_STATUS,
                R5_R05_GSM_SUBSCRIBER_TYPE,
                R5_R05_DATE_TIME_CAPTURED,
                R6_R06_ID,
                R6_R06_R05_ID_FK,
                R6_R06_R08_ID_FK,
                R6_R06_CHANGE_SOURCE,
                R6_R06_REFERENCE_NUMBER,
                R6_R06_DATE_TIME_CAPTURED,
                R6_R06_DATE_EXPIRED
            FROM
            (
            SELECT R5.R05_ID R5_R05_ID, 
                R5.R05_R01_ID_FK R5_R05_R01_ID_FK,
                R5.R05_NUMBER R5_NUMBER,
                R5.R05_NUMBER_TYPE R5_NUMBER_TYPE,
                R5.R05_STATUS R5_R05_STATUS,
                R5.R05_GSM_SUBSCRIBER_TYPE R5_R05_GSM_SUBSCRIBER_TYPE,
                R5.R05_DATE_TIME_CAPTURED R5_R05_DATE_TIME_CAPTURED,
                R6.R06_ID R6_R06_ID,
                R6.R06_R05_ID_FK R6_R06_R05_ID_FK,
                R6.R06_R08_ID_FK R6_R06_R08_ID_FK,
                R6.R06_STATUS R6_R06_STATUS,
                R6.R06_REFERENCE_NUMBER R6_R06_REFERENCE_NUMBER,
                R6.R06_DATE_TIME_CAPTURED R6_R06_DATE_TIME_CAPTURED,
                R6.R06_DATE_EXPIRED R6_R06_DATE_EXPIRED
              FROM R6_TABLE R6 , R5_TABLE R5
              WHERE R5.R05_ID = R6.R06_R05_ID_FK
              AND R5.R05_DATE_TIME_CAPTURED <= TRUNC(SYSDATE) - 1825
              AND R5.R05_STATUS = 'D'
              AND R6.R06_STATUS = 'D');       
    
            --selects all the R01 IDs which matches with the above criteria and copy values to respective archive tables
            SELECT UNIQUE R1.R01_ID AS R01_ID
            BULK COLLECT INTO R01_IDS                      
            FROM R1_TABLE R1, R5_TABLE R5
            WHERE R5.R05_ID IN (Select column_value from table(R05_IDS))
            AND R1.R01_ID NOT IN (
                                SELECT R01.R01_ID
                                FROM R1_TABLE R01,
                                       R5_TABLE R05
                                WHERE R05.R05_STATUS != 'D'
                                       AND R01.R01_ID = R05.R05_R01_ID_FK)
            AND R1.R01_ID = R5.R05_R01_ID_FK;   
    
            --insert R1_TABLE tables values which matches with the above criteria into the R1_TABLE_ARCHIVED table
            INSERT ALL
            INTO R1_TABLE_ARCHIVED(R01_ID,R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)  
            VALUES (RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED)
            SELECT RA1_R01_ID,RA1_R01_ID_TYPE,RA1_R01_IDENTITY_NUMBER,RA1_R01_PASSPORT_COUNTRY,RA1_R01_DATE_TIME_CAPTURED
            FROM (
                SELECT
                    r1.R01_ID  RA1_R01_ID,
                    r1.R01_ID_TYPE  RA1_R01_ID_TYPE,
                    r1.R01_IDENTITY_NUMBER  RA1_R01_IDENTITY_NUMBER,
                    r1.R01_PASSPORT_COUNTRY  RA1_R01_PASSPORT_COUNTRY,
                    r1.R01_DATE_TIME_CAPTURED  RA1_R01_DATE_TIME_CAPTURED
                    FROM
                    R1_TABLE r1
                    WHERE 
                    r1.R01_ID IN (Select column_value from table(R01_IDS))
            );
    
            --insert R2_TABLE tables values which matches with the above criteria into the R2_TABLE_ARCHIVED table
            INSERT ALL 
            INTO R2_TABLE_ARCHIVED(R02_ID,R02_R01_ID_FK,R02_fname,R02_SURNAME,R02_CONTACT_NUMBER,R02_DATE_TIME_CAPTURED)
            VALUES(RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED)
            SELECT  RA2_R02_ID,RA2_R02_R01_ID_FK,RA2_R02_fname,RA2_R02_SURNAME,RA2_R02_CONTACT_NUMBER,RA2_R02_DATE_TIME_CAPTURED
            FROM (
                    SELECT
                    r2.R02_ID  RA2_R02_ID,
                    r2.R02_R01_ID_FK  RA2_R02_R01_ID_FK,
                    r2.R02_fname  RA2_R02_fname,
                    r2.R02_SURNAME  RA2_R02_SURNAME,
                    r2.R02_CONTACT_NUMBER  RA2_R02_CONTACT_NUMBER,
                    r2.R02_DATE_TIME_CAPTURED  RA2_R02_DATE_TIME_CAPTURED
                    FROM
                    R2_TABLE r2
                    WHERE 
                    r2.R02_R01_ID_FK IN (Select column_value from table(R01_IDS)));     
    
    
            --All the delete queries to remove the above copied values from the parent tables respectively  
            DELETE FROM R1_TABLE WHERE R01_ID IN (Select column_value from table(R01_IDS));
            DELETE FROM R2_TABLE WHERE R02_R01_ID_FK IN (Select column_value from table(R01_IDS));
            DELETE FROM R5_TABLE WHERE R05_R01_ID_FK IN (Select column_value from table(R05_IDS));
            DELETE FROM R6_TABLE WHERE R06_R05_ID_FK IN (R05_IDS);      
    
    
        end loop;           
        close r5_cur;
        COMMIT;
    END;
    /
    

    Don't forget to scroll down for the END LOOP and to close the cursor!