Error: "ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT" While Running The ETL For Data Warehouse (Doc ID 2309442.1)

Last updated on OCTOBER 05, 2017

Applies to:

Symptoms

When attempting to run ETL process, it fails on step 50/56 part 2/2 the following error occurs:

ERROR
-----------------------
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at "STARUSER.DYNAMIC_UTIL", line 19
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at "STARUSER.DYNAMIC_UTIL", line 46
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at "STARUSER.SCD", line 410
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at "STARUSER.SCD", line 792
TIMESTAMP [Thread-1] INFO ETL.Message - Exception: Error during load for table W_CODES_RESOURCE_HD: ORA-04036: PGA memory used by t
TIMESTAMP [Thread-1] INFO ETL.Message - (100,W_CODES_RESOURCE_D,W_CODES_RESOURCE_HD,n/a,n/a)
TIMESTAMP [Thread-1] INFO ETL.Message - DECLARE
TIMESTAMP [Thread-1] INFO ETL.Message - *
TIMESTAMP [Thread-1] INFO ETL.Message - ERROR at line 1:
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at "STARUSER.SCD", line 843
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at "STARUSER.SCD", line 850
TIMESTAMP [Thread-1] INFO ETL.Message - ORA-06512: at line 4
TIMESTAMP [pool-3-thread-3] INFO ETL.Progress - Finished Script W_CODES_RESOURCE_HD.sql
TIMESTAMP [main] ERROR ETL.Message - The SQL script W_CODES_RESOURCE_HD.sql failed
TIMESTAMP [main] INFO ETL.Progress - Step 50/56 Part 2/2 - FAILED (1) (5 hours, 33 minutes, 48 seconds, 96 milliseconds)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run ETL process
2. Observe error on step 50 of 56

Changes

Cause