The migration went by the book. The initial load ran days ahead. On cutover night the final data refresh finished early, row counts matched table for table, the reconciliation report was green, and the application was pointed at the new database. Ten minutes later the first customer placed an order and got an error page:

ORA-00001: unique constraint (NEWSHOP.SYS_C008699) violated on table NEWSHOP.ORDERS columns (ID)

Nothing was lost and nothing was corrupted. Every row was there. The problem was a number that no data check looks at: the sequence that generates new order IDs was still where the initial load left it, days earlier, and it was handing out IDs the source had already used. This post is how that happens, why rehearsals tend to miss it, the check that catches it before go-live, and the fix.

Sequences are objects, not data

An Oracle sequence is a schema object that hands out numbers: ORDER_SEQ.NEXTVAL returns 9,001, then 9,002, and so on. The application uses it for new keys. Crucially, the sequence’s current position is part of its definition, not part of any table’s data. When you export a sequence, Data Pump writes a CREATE SEQUENCE … START WITH n statement, where n is where the sequence stood at that moment.

That one fact sets the trap. Many migrations work like this:

1. Initial load, days or weeks before cutover: export and import the whole schema, tables and sequences.
2. The source keeps running. Customers keep placing orders; ORDER_SEQ keeps advancing on the source.
3. Final refresh on cutover night: copy just the table data again, so the target catches up. With Data Pump that is CONTENT=DATA_ONLY with TABLE_EXISTS_ACTION=TRUNCATE; with other tools it’s a final catch-up load or replication.

Step 3 is deliberately data-only so it doesn’t touch the target’s objects, grants and code. Sequences are objects, so they are not touched either. The tables now contain every order up to 10,000; the sequence still thinks the next order is 9,001.

⤢ Enlarge

Why rehearsals miss it

Two things make this bug unusually good at surviving testing.

• Every data check passes. Row counts, checksums and reconciliation reports compare data. The data is correct. Nothing in a typical validation compares a sequence’s position to the keys in its table.
• Cached sequences hide it. With a cached sequence, Data Pump exports the sequence’s LAST_NUMBER, which is the top of its in-memory cache, not the last value actually used. In the lab’s first run, with CACHE 20 on a release that grows sequence caches dynamically, the source had used 9,000 values but the target sequence started at 22,221. A rehearsal with a few hundred test orders never reaches that headroom. Production, with days of real orders between initial load and cutover, does.

And when it does fail, it fails noisily but confusingly. A failed insert still consumes a NEXTVAL, so an application that retries walks the sequence forward one collision at a time. In the lab that is 1,000 failed orders before the sequence reaches a free key. Users see intermittent errors that “fix themselves”, which is the worst kind to diagnose under cutover pressure.

Prove it

The lab builds a source schema SHOP with 9,000 orders keyed by ORDER_SEQ, then runs the migration exactly as above with expdp/impdp: a full initial load into NEWSHOP, 1,000 more orders on the source, and a final CONTENT=DATA_ONLY refresh.

initial load    source  9,000 rows, max(id)  9,000 | target  9,000 rows, max(id)  9,000, ORDER_SEQ next 9,001
source drift    source 10,000 rows, max(id) 10,000 | target (unchanged)
final refresh   source 10,000 rows, max(id) 10,000 | target 10,000 rows, max(id) 10,000, ORDER_SEQ next 9,001
check           NEWSHOP.ORDER_SEQ  next=9001  max(ORDERS.ID)=10000  BEHIND by 1000
first insert    ORA-00001: unique constraint violated on table NEWSHOP.ORDERS columns (ID)
fix             ALTER SEQUENCE newshop.order_seq RESTART START WITH 10001
first insert    OK, new id = 10001

Don’t take my word for it — run it. The sequence-cutover lab runs this whole migration on Oracle Database Free with real expdp/impdp. It asserts that the row counts match after the final refresh, that the pre-go-live check flags the sequence as behind, that the first insert fails with ORA-00001, and that after the fix the same insert succeeds with a key above everything migrated. If any of those stops being true, the run fails. Proven on every CI push.

The check: run it before you open the doors

For every sequence that feeds a key, compare the value it will hand out next with the highest key already in the table. Oracle doesn’t record which sequence feeds which column (unless it’s a column DEFAULT or an identity column), so the mapping belongs in the cutover runbook. The check itself is short:

-- next value each sequence will hand out vs the highest key in the table it feeds
SELECT s.sequence_owner, s.sequence_name, s.last_number AS next_value,
       (SELECT MAX(id) FROM newshop.orders) AS max_key,
       CASE WHEN s.last_number <= (SELECT MAX(id) FROM newshop.orders)
            THEN 'BEHIND' ELSE 'ok' END AS status
FROM   dba_sequences s
WHERE  s.sequence_owner = 'NEWSHOP' AND s.sequence_name = 'ORDER_SEQ';

The lab’s version loops over a list of sequence => table.column pairs, so one run covers every sequence in the application. Any BEHIND row is a guaranteed ORA-00001 waiting for its first insert.

The fix

Move each lagging sequence past the highest existing key:

ALTER SEQUENCE newshop.order_seq RESTART START WITH 10001;   -- max(id) + 1

RESTART START WITH (18c and later) resets the sequence in place, so grants, synonyms and dependent code stay intact. On older releases, either temporarily change INCREMENT BY to the gap, call NEXTVAL once and set it back, or drop and recreate the sequence and re-grant it. For identity columns, Oracle can do the arithmetic for you: ALTER TABLE newshop.orders MODIFY id GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) moves the identity’s next value past the current maximum.

The durable fix is in the runbook, not the database:

• Make “resync sequences” an explicit cutover step, after the final data load and before go-live, using max(key) + 1 (plus a margin if anything else might still write).
• Or re-export sequences with the final data. Include the sequence objects in the final refresh and replace the target’s copies, so they carry the source’s position at cutover time.
• Run the check as a go/no-go gate. A cutover isn’t done when the rows match. It’s done when the sequence check reports no BEHIND.

What teams get wrong

• Treating “row counts match” as “migration verified”. Data checks prove the data. They say nothing about the objects that generate the next row.
• Rehearsing with too little traffic. A cached sequence’s export position includes headroom. If the rehearsal doesn’t generate more keys between initial load and refresh than that headroom, the bug never shows.
• Fixing it by dropping the primary key “temporarily”. That turns a loud error into silent duplicate IDs. Fix the sequence, never the constraint.
• Resetting to exactly max(id). START WITH is the next value handed out, so it must be max(id) + 1. RESTART START WITH max(id) produces one more collision.
• Forgetting sequences that don’t feed a primary key. Invoice numbers, external reference IDs and other unique business keys come from sequences too, and collide the same way.

Frequently asked questions

Why do I get ORA-00001 right after a database migration?

Usually because a sequence on the new database is behind the data. Migrations often load the schema once, including sequences, while the source keeps running, then refresh only the table data at cutover. The tables end up with every row, but each sequence is still at the position it had at the initial load, so its next values are keys that already exist in the table. The first inserts that use those sequences fail with ORA-00001 unique constraint violated. Nothing is lost or corrupted; the sequences just need to be moved past the highest existing key.

Does Data Pump migrate sequence values?

Data Pump exports a sequence as a CREATE SEQUENCE statement whose START WITH value is the sequence's position at export time, specifically its LAST_NUMBER, which for a cached sequence is the top of the cache rather than the last value actually used. So a full export and import does carry the sequence position as of the moment of the export. What it cannot do is follow the source after that: if the source keeps generating values and you later refresh only table data (for example with CONTENT=DATA_ONLY), the target sequences stay at the old position.

How do I check whether sequences are behind after a migration?

For each sequence that feeds a key, compare the value it will hand out next with the maximum key in the table it feeds. DBA_SEQUENCES.LAST_NUMBER gives the next value that has not been cached yet, and SELECT MAX(column) gives the highest existing key; if LAST_NUMBER is less than or equal to the maximum, the sequence is behind and will produce a duplicate key. Oracle does not record which sequence feeds which column unless the column uses it as a DEFAULT or is an identity column, so keep the sequence-to-column mapping in the cutover runbook and run the check as a go/no-go step before go-live.

How do I reset an Oracle sequence to a specific value?

On Oracle 18c and later, use ALTER SEQUENCE owner.name RESTART START WITH n, where n is the next value you want handed out, typically the maximum existing key plus one. It resets the sequence in place and keeps its grants and dependents. On older releases, either temporarily set INCREMENT BY to the size of the gap, select NEXTVAL once, and set INCREMENT BY back to its original value, or drop and recreate the sequence with the right START WITH and re-grant it.

What about identity columns?

Identity columns are backed by an internal sequence and have the same problem if the table data is reloaded without moving that sequence. Oracle provides a direct fix: ALTER TABLE table_name MODIFY column_name GENERATED BY DEFAULT AS IDENTITY (START WITH LIMIT VALUE) sets the identity's next value past the current maximum value in the column. Use the GENERATED clause that matches how the column is defined (ALWAYS or BY DEFAULT). You can see identity definitions in USER_TAB_IDENTITY_COLS.

Why didn't the problem show up in the migration rehearsal?

Two reasons are common. First, rehearsals validate data (row counts, checksums, reconciliation), and the data really is correct; nothing compares sequence positions to keys. Second, cached sequences are exported at the top of their cache, which can be well ahead of the last value actually used, especially on releases that grow sequence caches dynamically. A rehearsal that generates only a little test activity between the initial load and the refresh never uses up that headroom, while days of production traffic do.

Can I fix it by disabling the primary key constraint?

No. Disabling or dropping the constraint makes the error disappear by allowing duplicate keys, which silently corrupts the data model and breaks anything that relies on the key being unique. The constraint is doing its job by refusing duplicates. The correct fix is to move the sequence past the highest existing key with ALTER SEQUENCE ... RESTART START WITH max plus one, and to make sequence resynchronization an explicit step in the cutover runbook.

Sequence drift is the third of the migration failures that sail through a “successful” cutover, alongside Data Pump jobs nobody counted and character-set conversions that quietly damage data. The pattern is the same each time: the tooling reports success, so verify the thing the tooling doesn’t check. Choose the method with the migration decision guide, then make sequence resync and its check explicit steps in the cutover runbook. And prove it the way that ends the argument: with the sequence-cutover lab, where every row arrives intact and the first new order still fails until the sequence is moved.