3

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;
APC
  • 144,005
  • 19
  • 170
  • 281
sher17
  • 607
  • 1
  • 12
  • 31

2 Answers2

1

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!

APC
  • 144,005
  • 19
  • 170
  • 281
  • thank you very much for the clear explanation. this code ran for more than an hour in my database and returned with the error ORA-01653: unable to extend table R5_TABLE_archived by 8192 in tablespace USERS 01653. 00000 - "unable to extend table %s.%s by %s in tablespace %s" *Cause: Failed to allocate an extent of the required number of blocks for a table segment in the tablespace indicated. *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated. – sher17 Dec 14 '17 at 11:15
  • is there any workaround for the tablespace issue occurring here – sher17 Dec 14 '17 at 11:20
  • To be clear, I have 31931421 records in the R5 table meeting the requirements to be moved. – sher17 Dec 14 '17 at 11:27
1

Tablespace is a mapping to physical storage. There's not much in the way of workaround except asking your DBA to add another data file.

APC
  • 144,005
  • 19
  • 170
  • 281