R12 - Sequence EGO.EGO_IMPORT_ROW_SEQ_S, MRP.MRP_SCHEDULE_DATES_S and MRP.MRP_RELIEF_INTERFACE_S: ORA-08004 exceeds MAXVALUE and cannot be instantiated

Environment: R12.1.2 on Linux x-86

Three of the sequences in our environment were closer to its MAX VALUE. We worked with our internal tech team and Oracle Support to perform the following steps. Before making any changes to the sequence, it is always recommended to query ALL_SEQUENCES/DBA_SEQUENCES and also backup the Sequence creation script using Toad/SQL Developer.

EGO.EGO_IMPORT_ROW_SEQ_S

SELECT * from DBA_SEQUENCES where sequence_name='EGO_IMPORT_ROW_SEQ_S';

ALTER sequence EGO.EGO_IMPORT_ROW_SEQ_S increment by -2147483646 minvalue 1;
SELECT EGO.EGO_IMPORT_ROW_SEQ_S.nextval from dual;
ALTER sequence EGO.EGO_IMPORT_ROW_SEQ_S increment by 1;
SELECT EGO.EGO_IMPORT_ROW_SEQ_S.currval from dual;

SELECT * from DBA_SEQUENCES where sequence_name='EGO_IMPORT_ROW_SEQ_S';

TRUNCATE table INV.MTL_SYSTEM_ITEMS_INTERFACE;
TRUNCATE table INV.MTL_ITEM_REVISIONS_INTERFACE;
TRUNCATE table EGO.EGO_ITM_USR_ATTR_INTRFC;
TRUNCATE table BOM.BOM_BILL_OF_MTLS_INTERFACE;
TRUNCATE table INV.MTL_INTERFACE_ERRORS;
TRUNCATE table BOM.BOM_INVENTORY_COMPS_INTERFACE;


MRP.MRP_SCHEDULE_DATES_S

SELECT * from DBA_SEQUENCES where sequence_name='MRP_SCHEDULE_DATES_S';

SELECT min(mps_transaction_id), max(mps_transaction_id), schedule_designator, organization_id
FROM mrp_schedule_dates
GROUP BY organization_id, schedule_designator
ORDER BY min(mps_transaction_id);

You may want to take a look at the minimum transaction id's and see if you're going to run into the ORA-00001 down the road because we are going to be setting the sequence back to 1. Purge any old MPS/MDS schedules that are no longer used. This will help clean up the table mrp_schedule_dates and get rid of old transaction_id's.

Please backup the Sequence creation script using TOAD or SQL Developer.

DROP sequence MRP.MRP_SCHEDULE_DATES_S;
CREATE SEQUENCE MRP.MRP_SCHEDULE_DATES_S MINVALUE 1 MAXVALUE 2147483647 INCREMENT BY 1 START WITH 1 CACHE 1000 NOORDER NOCYCLE;
commit;
SELECT * from DBA_SEQUENCES where sequence_name='MRP_SCHEDULE_DATES_S';

MRP.MRP_RELIEF_INTERFACE_S

SELECT * from DBA_SEQUENCES where sequence_name='MRP_RELIEF_INTERFACE_S';

Please backup the Sequence creation script using TOAD or SQL Developer.

DROP sequence MRP.MRP_RELIEF_INTERFACE_S;
CREATE SEQUENCE MRP.MRP_RELIEF_INTERFACE_S MINVALUE 1 MAXVALUE 2147483647 INCREMENT BY 1 START WITH 1 CACHE 1000 NOORDER NOCYCLE;
commit;
TRUNCATE table MRP.MRP_RELIEF_INTERFACE;
commit;
SELECT * from DBA_SEQUENCES where sequence_name='MRP_RELIEF_INTERFACE_S';

Reference:
MRCSAL1 Load/Copy/Merge MDS Errors ORA-08004: Sequence Mrp_schedule_dates_s.Nextval Exceeds Maxvalue Also Discussion To Avoid ORA-00001 (Doc ID 1401177.1)