SQL & PL/SQL

Hi,

I have a table with 4Million rows that i want to insert. I am using INSERT/SELECT Clause to accomplish this, for some reason after a specific time the locks gets released and the system hangs and the query doesn't complete.

I have employed the following apporaches for this problem and none of them worked

1) I loaded the rownum, rowid from the source table onto a lookup table and selected from the source table in batches of 100,000 records and committed them. I donot see any activity after inserting 400,000 rows. locks gets released.

2) I removed Parallelised insert hints thinking there could be some table segment header contention and that didn't work either. Our tablespace is set up for auto management.

3) I used CTAS( Create Table as Select) Again same issue here. The locks gets released after some time and query hangs.

I am running out of options and not sure what the issue is.

Here is the sql

mySQL := 'INSERT /*+ PARALLEL(' || tgtCwsSchema || '.' || 'TRN_RESERVE,4,1) */ INTO ' || tgtCwsSchema || '.' || 'TRN_RESERVE(RESERVE_ID, CLAIM_ID,'
|| ' RESERVE_TYPE_ID, RESERVE_STATUS, RESERVE_STATUS_DT, FOLDER_TYPE_CODE, COVERAGE_ID, LOSS_TYPE_ID, ORIG_RESERVE_AMT, RESERVE_TO_DT_AMT,'
|| ' PAID_TO_DT_AMT, AMOUNT_CURRENCY, AVERAGED_RESERVE, RESERVE_DT, MONETARY_CATEGORY, PRE_APPROVE_F, APPROVE_STATUS, APPROVE_DT, UPDATED_BY,'
|| ' INSERT_DT, UPDATE_DT, DELETED_F)'
|| ' SELECT /*+ ALL_ROWS PARALLEL(' || srcSchema || '.TRN_RESERVE,4, 1) */ T1.RESERVE_ID, T1.WORK_ID,'
|| ' (SELECT MIN(a.RESERVE_TYPE_ID) FROM '|| tgtCwsSchema || '.SYS_RESERVE_TYPE_L a'
|| ' WHERE TRIM(a.RESERVE_TYPE) = TRIM(T1.RESERVE_TYPE) ) RESERVE_TYPE_ID, TRIM(T1.RESERVE_STATUS) RESERVE_STATUS, T1.RESERVE_STATUS_DT,'
|| ' T1.FOLDER_TYPE_CODE, T1.COVERAGE_ID, (SELECT LT.LOSS_TYPE_ID FROM ' || tgtCwsSchema || '.SYS_LOSS_TYPE LT, '
|| tgtCwsSchema || '.SYS_LOSS_TYPE_L LTL WHERE LT.LOSS_TYPE_ID = LTL.LOSS_TYPE_ID AND LT.LOB_ID = T2.LOB_ID'
|| ' AND LT.LOSS_TYPE_CATEGORY = T7.LOSS_TYPE_CATEGORY'
|| ' AND LT.MF_CODE = T1.LOSS_MF_CODE AND LTL.LOSS_TYPE = T1.LOSS_TYPE) LOSS_TYPE_ID,'
|| ' (SELECT NVL(TT.AMOUNT,0) FROM ' || srcSchema ||'.TRN_TRANSACTION TT'
|| ' WHERE TT.RESERVE_ID=T1.RESERVE_ID AND TT.TRAN_SEQ_NUM=1) ORIG_RESERVE_AMT,'
|| ' PKG_CWS_PHASE_3.FN_COMPUTE_RESERVES(T1.RESERVE_ID) RESERVE_TODATE_AMT, PKG_CWS_PHASE_3.FN_GET_PAID_AMT PAID_TODATE_AMT, '
|| ' ''USD'' AS AMOUNT_CURRENCY,'
|| ' (SELECT decode(AVERAGED_RESERVE,1,''Y'',0,''N'') FROM ' || srcSchema ||'.TRN_TRANSACTION TT'
|| ' WHERE TT.RESERVE_ID=T1.RESERVE_ID AND TT.TRAN_SEQ_NUM=1) AVERAGED_RESERVE,'
|| ' T1.RESERVE_DT, TRIM(T1.MONETARY_CATEGORY) MONETARY_CATEGORY,'
|| ' DECODE(T1.PREAPPROVED_STATUS,1,''Y'',0,''N'') PRE_APPROVE_F,'
|| ' (SELECT APPROVE_STATUS FROM ' || srcSchema || '.TRN_TRANSACTION'
|| ' WHERE TRAN_SEQ_NUM=(SELECT MAX(TRAN_SEQ_NUM) FROM ' || srcSchema || '.TRN_TRANSACTION WHERE RESERVE_ID=T1.RESERVE_ID )'
|| ' AND RESERVE_ID=T1.RESERVE_ID) APPROVE_STATUS,'
|| ' (SELECT APPROVE_DT FROM ' || srcSchema || '.TRN_TRANSACTION '
|| ' WHERE TRAN_SEQ_NUM=(SELECT MAX(TRAN_SEQ_NUM) FROM ' || srcSchema || '.TRN_TRANSACTION WHERE RESERVE_ID=T1.RESERVE_ID )'
|| ' AND RESERVE_ID=T1.RESERVE_ID) APPROVE_DT,'
|| ' NVL((SELECT NVL(CREATOR_ID,4) FROM ' || srcSchema || '.TRN_TRANSACTION '
|| ' WHERE TRAN_SEQ_NUM=(SELECT MAX(TRAN_SEQ_NUM) FROM ' || srcSchema || '.TRN_TRANSACTION WHERE RESERVE_ID=T1.RESERVE_ID )'
|| ' AND RESERVE_ID=T1.RESERVE_ID),4) UPDATED_BY,'
|| ' T1.INSERT_DT, T1.UPDATE_DT, ''N'' DELETED_F '
|| ' FROM '
|| srcSchema || '.TRN_RESERVE T1, ' || srcSchema || '.WFM_WORK_ITEM T2, '
|| srcSchema || '.TRN_COVERAGE T3, ' || srcSchema || '.CLM_ROLE T4, '
|| srcSchema || '.CLM_UNIT T5, ' || srcSchema || '.SYS_VALID_COVERAGE T6, '
|| srcSchema || '.SYS_VALID_RESERVE T7'
|| ' WHERE T1.WORK_ID = T2.WORK_ID '
|| ' AND T1.COVERAGE_ID = T3.COVERAGE_ID'
|| ' AND T3.ROLE_ID = T4.ROLE_ID'
|| ' AND T4.UNIT_ID = T5.UNIT_ID'
|| ' AND T2.LOB_ID = T6.LOB_ID'
|| ' AND T5.UNIT_TYPE = T6.UNIT_TYPE'
|| ' AND T5.UNIT_SUBTYPE = T6.UNIT_SUBTYPE'
|| ' AND T4.ROLE_TYPE = T6.ROLE_TYPE'
|| ' AND T3.COVERAGE_TYPE = T6.COVERAGE_TYPE'
|| ' AND T6.LOB_ID = T7.LOB_ID'
|| ' AND T6.RESERVE_CATEGORY = T7.RESERVE_CATEGORY'
|| ' AND T1.RESERVE_TYPE = T7.RESERVE_TYPE';