The only conflict of sequence after logical migration of data pump

Preface

Summing up an old topic, when using data pump to perform logical import, we often encounter the problem of unique conflict in sequence. This paper summarizes the solutions.

Reason

When impdp performs an import, it imports sequence sequentially (assuming currval is 100 at this time) and then imports the table, but if someone inserts data into the table or otherwise causes sequence to be called, currval increases to 1000 at this time. After the data is imported, the value of sequence starts at 100, and the sequence of 100-1000 has been occupied, resulting in a unique conflict

Solution:

1. Drop the sequence on the destination side, obtain the sequence metadata from the source side, and recreate it with the dest

two。 Database as for RESTRICT SESSION mode, perform export

3. Set the CACHE of SEQUENCE to a large value to ensure that the value of the sequence CACHE will not be used up in the time before exporting the sequence and the table data, which ensures that the NEXTVAL of the sequence value is not smaller than the existing data in the table after import.