ETL Hangs When Running the "load_resource_limit.sql" or "load_role_limit.sql" Script (Doc ID 2836295.1)

Last updated on MARCH 16, 2022

Applies to:

Symptoms

When running the ETL process, the load_resource_limit.sql and/or load_role_limit.sql scripts run for a long time.  In some ETL releases, this is step 51 or step 65.

In this example from the ETL log file, only load_resource_limit.sql has completed; load_role_limit.sql is still running (and may run for hours):

2022-01-25 19:20:11.705 [main] INFO ETL.Message - === Step 65, Part 1 ====================================================
2022-01-25 19:20:11.707 [main] INFO ETL.Message - === Load ResourceLimit and RoleLimit Tables (res_role_limit)
2022-01-25 19:20:11.772 [pool-2-thread-1] INFO ETL.Message - Adding sql script load_role_limit.sql to the job queue
2022-01-25 19:20:11.774 [pool-2-thread-1] INFO ETL.Progress - Starting Script load_role_limit.sql
2022-01-25 19:20:11.784 [pool-2-thread-2] INFO ETL.Message - Adding sql script load_resource_limit.sql to the job queue
2022-01-25 19:20:11.790 [pool-2-thread-2] INFO ETL.Progress - Starting Script load_resource_limit.sql
2022-01-25 19:35:49.281 [Thread-3] INFO ETL.Message - ResourceLimitLoad Insert Time for 23362981 rows on table W_RESOURCE_LIMIT_F is
2022-01-25 19:35:49.285 [Thread-3] INFO ETL.Message - 15.613 mins (936.8s) at rate 24939.134/s
2022-01-25 19:35:49.293 [Thread-3] INFO ETL.Message - Complete load on W_RESOURCE_LIMIT_F for table W_RESOURCE_LIMIT_F rows=23362981
2022-01-25 19:35:49.317 [Thread-3] INFO ETL.Message - Processed 23362981 rows in 936.803/s at rate of 24939.054/s
2022-01-25 19:35:49.328 [Thread-3] INFO ETL.Message - PL/SQL procedure successfully completed.
2022-01-25 19:35:50.345 [pool-2-thread-2] INFO ETL.Progress - Finished Script load_resource_limit.sql

Changes

Cause