SQL Error: ORA-30926: unable to get a stable set of rows in the source tabl
Hi All,
I have two tables CBT & CBTH with 297 columns and 6 columns are used as composite primary key. Table structure of both tables is same difference being in data. CBT stores current month CBTH stores previous month data.
There is a column called CR_limit_last_month in CBT which need to be updated with CR_limit_curr_month values from CBTH.
Would Merge statement help???
Please suggest best of acheiving this. I tried with update but it is long time as both tables contain millions of records.
Error starting at line 1 in command:
MERGE INTO CBTABLE CBT USING
(SELECT DATE_BUSINESS_OPERATION,CHART_OF_ACCOUNT,CURRENCY_CODE,COST_CENTER,CUST_ACCOUNT_NUMBER,REFERENCE_NUMBER_KEY,
SELL_PURCHASE_IND,ACCOUNT_OFFICER,CENTRAL_BANK_INDICATOR,CR_LIMIT_KDE FROM CBTABLE_H WHERE DATE_BUSINESS_OPERATION='31-DEC-07')
CBTH
ON
(CBT.CHART_OF_ACCOUNT=CBTH.CHART_OF_ACCOUNT AND
NVL(CBT.CURRENCY_CODE,'N')=NVL(CBTH.CURRENCY_CODE,'N') AND
NVL(CBT.COST_CENTER,'0')=NVL(CBTH.COST_CENTER,'0') AND
NVL(CBT.CUST_ACCOUNT_NUMBER,'N')=NVL(CBTH.CUST_ACCOUNT_NUMBER,'N') AND
NVL(CBT.REFERENCE_NUMBER_KEY,'N')=NVL(CBTH.REFERENCE_NUMBER_KEY,'N') AND
NVL(CBT.SELL_PURCHASE_IND,'N')=NVL(CBTH.SELL_PURCHASE_IND,'N') AND
NVL(CBT.ACCOUNT_OFFICER,'N')=NVL(CBTH.ACCOUNT_OFFICER,'N') AND
NVL(CBT.CENTRAL_BANK_INDICATOR,'N')=NVL(CBTH.CENTRAL_BANK_INDICATOR,'N')
)
WHEN MATCHED THEN
UPDATE SET CBT.CR_LIMIT_LAST_KDE=CBTH.CR_LIMIT_KDE
Error report:
SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
30926. 00000 - "unable to get a stable set of rows in the source tables"
*Cause: A stable set of rows could not be got because of large dml
activity or a non-deterministic where clause.
*Action: Remove any non-deterministic where clauses and reissue the dml.