ETL Fails With "ORA-00001: unique constraint (STARUSER.NDX_W_ACTIVITY_SPREAD_F_ADDWD)" (Doc ID 2822196.1)

Last updated on APRIL 24, 2023

Applies to:

Symptoms

When runing the ETL process, the following error occurs.

ERROR

10:40:16.618 [main] INFO ETL.Message - === Step 46, Part 1 ====================================================
10:40:16.619 [main] INFO ETL.Message - === Load activity and resource assignment spreads (fact_load)
10:40:16.630 [pool-2-thread-1] INFO ETL.Message - Adding sql script res_assignment_period_actual.sql to the job queue
10:40:16.631 [pool-2-thread-1] INFO ETL.Progress - Starting Script res_assignment_period_actual.sql
10:40:18.510 [Thread-3] INFO ETL.Message - PL/SQL procedure successfully completed.
10:40:19.326 [pool-2-thread-1] INFO ETL.Progress - Finished Script res_assignment_period_actual.sql
10:40:19.328 [pool-3-thread-1] INFO ETL.Message - Adding sql script activity_hist_spread.sql to the job queue
10:40:19.328 [pool-3-thread-1] INFO ETL.Progress - Starting Script activity_hist_spread.sql
10:40:19.329 [pool-3-thread-2] INFO ETL.Message - Adding sql script activity_period_actual.sql to the job queue
10:40:19.329 [pool-3-thread-2] INFO ETL.Progress - Starting Script activity_period_actual.sql
10:40:19.330 [pool-3-thread-3] INFO ETL.Message - Adding sql script activity_spread.sql to the job queue
10:40:19.330 [pool-3-thread-3] INFO ETL.Progress - Starting Script activity_spread.sql
10:40:19.330 [pool-3-thread-4] INFO ETL.Message - Adding sql script resource_assignment_hist_spread.sql to the job queue
10:40:19.331 [pool-3-thread-4] INFO ETL.Progress - Starting Script resource_assignment_hist_spread.sql
10:40:19.331 [pool-3-thread-5] INFO ETL.Message - Adding sql script resource_assignment_spread.sql to the job queue
10:40:19.331 [pool-3-thread-5] INFO ETL.Progress - Starting Script resource_assignment_spread.sql
10:40:21.296 [Thread-13] INFO ETL.Message - PL/SQL procedure successfully completed.
10:40:21.831 [Thread-5] INFO ETL.Message - LoadActivitySpread Insert Time for 0 rows on table W_ACTIVITY_SPREAD_HF is .009
10:40:21.831 [Thread-5] INFO ETL.Message - mins (.54s) at rate 0/s
10:40:21.831 [Thread-5] INFO ETL.Message - Complete extract on W_ACTIVITY_SPREAD_HF for table W_ACTIVITY_SPREAD_HF rows=0
10:40:21.831 [Thread-5] INFO ETL.Message - Processed 0 rows in .541/s at rate of 0/s
10:40:21.832 [Thread-5] INFO ETL.Message - PL/SQL procedure successfully completed.
10:40:22.057 [pool-3-thread-1] INFO ETL.Progress - Finished Script activity_hist_spread.sql
10:40:22.085 [pool-3-thread-2] INFO ETL.Progress - Finished Script activity_period_actual.sql
10:40:23.085 [Thread-9] INFO ETL.Message - LoadResourceAssignment Insert Time for 0 rows on table W_ASSIGNMENT_SPREAD_HF is
10:40:23.085 [Thread-9] INFO ETL.Message - .03 mins (1.79s) at rate 0/s
10:40:23.085 [Thread-9] INFO ETL.Message - Complete extract on W_ASSIGNMENT_SPREAD_HF for table W_ASSIGNMENT_SPREAD_HF
10:40:23.085 [Thread-9] INFO ETL.Message - rows=0
10:40:23.085 [Thread-9] INFO ETL.Message - Processed 0 rows in 1.797/s at rate of 0/s
10:40:23.085 [Thread-9] INFO ETL.Message - PL/SQL procedure successfully completed.
10:40:24.062 [pool-3-thread-4] INFO ETL.Progress - Finished Script resource_assignment_hist_spread.sql
10:40:27.616 [Thread-11] INFO ETL.Message - ORA-06512: at "STARUSER.FACT_LOAD_PKG", line 702
10:40:27.616 [Thread-11] INFO ETL.Message - Exception: Error during extract for table W_ACTIVITY_SPREAD_F: ORA-00001: unique
10:40:27.616 [Thread-11] INFO ETL.Message - constraint
10:40:27.616 [Thread-11] INFO ETL.Message - (100,W_ACTIVITY_SPREAD_FS,W_ACTIVITY_SPREAD_F,n/a,n/a)
10:40:27.617 [Thread-11] INFO ETL.Message - declare
10:40:27.617 [Thread-11] INFO ETL.Message - *
10:40:27.617 [Thread-11] INFO ETL.Message - ERROR at line 1:
10:40:27.617 [Thread-11] INFO ETL.Message - ORA-00001: unique constraint (STARUSER.NDX_W_ACTIVITY_SPREAD_F_ADDWD) violated
10:40:27.617 [Thread-11] INFO ETL.Message - ORA-06512: at "STARUSER.FACT_LOAD_PKG", line 830
10:40:27.617 [Thread-11] INFO ETL.Message - ORA-06512: at "STARUSER.FACT_LOAD_PKG", line 702
10:40:27.618 [Thread-11] INFO ETL.Message - ORA-06512: at line 4
10:40:28.065 [pool-3-thread-3] INFO ETL.Progress - Finished Script activity_spread.sql
10:40:51.523 [Thread-7] INFO ETL.Message - ORA-06512: at "STARUSER.FACT_LOAD_PKG", line 925
10:40:51.523 [Thread-7] INFO ETL.Message - Exception: Error during load for table W_RESOURCE_ASSIGNMENT_SPREAD_F:
10:40:51.524 [Thread-7] INFO ETL.Message - ORA-00001: unique co
10:40:51.524 [Thread-7] INFO ETL.Message - (100,W_RESOURCE_ASSIGNMENT_SPREAD_FS,W_RESOURCE_ASSIGNMENT_SPREAD_F,n/a,n/a)
10:40:51.524 [Thread-7] INFO ETL.Message - declare
10:40:51.524 [Thread-7] INFO ETL.Message - *
10:40:51.524 [Thread-7] INFO ETL.Message - ERROR at line 1:
10:40:51.524 [Thread-7] INFO ETL.Message - ORA-12899: value too large for column
10:40:51.525 [Thread-7] INFO ETL.Message - "STARUSER"."ETL_PROCESSEXCEPTION"."PMDBTABLENAME" (actual: 31, maximum: 30)
10:40:51.525 [Thread-7] INFO ETL.Message - ORA-06512: at "STARUSER.STATUS_ADD_EXCEPTION", line 28
10:40:51.525 [Thread-7] INFO ETL.Message - ORA-06512: at "STARUSER.STATUS_ADD_EXCEPTION_STACK", line 12
10:40:51.525 [Thread-7] INFO ETL.Message - ORA-06512: at "STARUSER.FACT_LOAD_PKG", line 1011
10:40:51.525 [Thread-7] INFO ETL.Message - ORA-00001: unique constraint (STARUSER.NDX_W_RSRC_ASSGN_SPRD_F_RDWID) violated
10:40:51.525 [Thread-7] INFO ETL.Message - ORA-06512: at "STARUSER.FACT_LOAD_PKG", line 925
10:40:51.526 [Thread-7] INFO ETL.Message - ORA-06512: at line 4
10:40:52.075 [pool-3-thread-5] INFO ETL.Progress - Finished Script resource_assignment_spread.sql
10:40:52.084 [main] INFO ETL.Progress - Step 46/78 Part 1/1 - DONE (0 hours, 0 minutes, 36 seconds, 735 milliseconds)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.