Why Oracle Data Pump Export Skews Sequence Values and How to Resolve It

Problem Overview

A colleague noticed that after using expdp to export a schema, a table whose primary key is generated by a trigger‑based sequence ended up with data values larger than the current sequence value, causing insert errors during import.

Root Cause

During export, Oracle first dumps the sequence definition and then the table data. If the table continues to receive inserts while the export is in progress, the sequence value can lag behind the inserted rows, leading to the mismatch.

Solution

Recreate the sequence used by the trigger, setting its start value to MAX(primary_key)+1 of the table after import.

Step‑by‑Step Demonstration

Prepare the Test Environment Create a test table, a sequence, a trigger that uses the sequence, and a stored procedure that simulates continuous inserts.

Simulate Continuous Inserts Run the stored procedure in an infinite loop so that one row is inserted every second until manually stopped.

Export with Data Pump Execute expdp to export the jingyu schema to a directory. Export output screenshots:

Import into a New Schema Use impdp to load the dump file into a new user jingyu2 . Import output screenshots:

Reproduce the Issue and Apply the Fix After import, the maximum BOOKID in the table exceeds the current sequence value, as shown in the query result screenshot. Compilation warnings appear for the imported stored procedure due to permission issues; the article shows the warning screenshots. After fixing permissions, the procedure compiles but still fails on insert because the sequence is behind. Recreate the sequence with START WITH MAX(BOOKID)+1 and the inserts succeed. SQL to recreate the sequence (shown in the final screenshot):

Conclusion

The mismatch occurs because Data Pump exports the sequence before the table data, allowing concurrent inserts to advance the primary‑key values beyond the sequence. Resetting the sequence after import to the maximum key value resolves the error.