How To Reset When Hit Maximum Reservation Sequence (MTL_RESERVATIONS_S)? (Doc ID 1548434.1)

Last updated on JULY 22, 2024

Applies to:

Goal

How to reset the reservation sequence id (MTL_RESERVATIONS_S) when the max value is hit?

Background
Using MRP and as of R12.1.3, the reservation id was doubled and stored in an MRP sequence value with a 2 billion limit.
Any reservation sequence over 1 billion was causing an error.

(See <Note 1569691.1> - "Memory Based Snapshot Worker Error Ora-01455 mrsres_load_reservations"
and Related <BUG:16708645> - "MEMORY BASED SNAPSHOT WORKER ERRORS MRSRES_LOAD_RESERVATIONS ORA-01455".)

EXAMPLE

Example related sequences:

EXPECTED BEHAVIOR / ASSISTANCE
Need datafix to reset the reservation id to 1.
Expect that need to reset MTL_DEMAND_S.

STEPS
1. Enable MRP...
2. Use reservations with 100,000 orders per day.
3. Reach 1 billion reservations.
4. Get MRP errors.

MRP ERRORS
ASCP Planning - Unconstrained & Constrained/Plan concurrent requests are
erroring
Memory Based Snashot Worker Error Out with Ora-01455

BUSINESS IMPACT
Perform 100,000 orders per day. Any small issue could have huge consequences.
Want to make sure no negative impact to change and no over-site.
If not fixed, MRP gives errors.

Solution

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