ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

 Error:-

IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1)

SYMPTOMS:-

You may have noticed that IEX_DELINQUENCIES_ALL is not getting updated with closed delinquencies or new delinquencies on past due invoices when the IEX: 

Scoring Engine Harness or IEX: Delinquency Management processes run.  You have confirmed that you are not putting in a value for the Maximum Number of Records parameter on the IEX: Scoring Engine Harness, however no delinquencies are being created.

On further investigation, even though IEX: Scoring Engine Harness shows successful the following message can be seen in the log -

ERROR IN CONCUR: IEX_SCORE: storeDelBuffest: UnExpected Error ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE and cannot be instatiated

CAUSE:-

The IEX_DELINQUENCIES_ALL table has run out of sequence numbers and you can no longer create new records.

Bug 21812472

SOLUTION:-

To resolve the issue, the following SQL will recreate the sequence IEX_DEL_BUFFERS_S with NO Max Value:

set serveroutput on;

declare

BEGIN

execute immediate ('drop sequence IEX.iex_del_buffers_s');

execute immediate ('create sequence IEX.IEX_DEL_BUFFERS_S START WITH 10000 '

  || 'MINVALUE 1 INCREMENT BY 1 CACHE 100 NOORDER ');

 DBMS_OUTPUT.put_line('New Sequence for IEX_DEL_BUFFERS_S ' || sqlerrm);

EXCEPTION

  WHEN OTHERS then

  DBMS_OUTPUT.put_line('Sequence creation failed IEX_DEL_BUFFERS_S ' ||

sqlerrm);

end;

commit;

Output like below:-

New Sequence for IEX_DEL_BUFFERS_S ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

Commit complete.