[SQL] Oracle align sequence values after database import

In Oracle, after you import a database which had sequences in it, you may incur in violations of some primary key constraints when attempting to insert new data using said sequences.

This happens because the sequence values are not aligned with the DB tables so the next value the sequence will offer may be already in use. To check this you can:

SELECT MAX(key) FROM table;

To get the last sequence value used for that table key, then:

SELECT sequence_name.NextVal FROM DUAL;

To see where is the sequence at now. If that value is lower than the one you got from the last query, you'll have to correct the sequence. You can do so in many ways:

1- Drop and recreate the sequence with a new (correct) START WITH value:

DROP SEQUENCE sequence_name;

CREATE SEQUENCE sequence_name
START WITH new_correct_value--eg key+1
MAXVALUE how_high_can_it_go
MINVALUE how_low_can_it_be
[other additional parameters based on your needs];

2a- Query the sequence until you reach the desired value reusing multiple times the query we ran to check where it was.

2b- Alter the sequence increment step (either up or down) then query it for a new value, forcing it to reach your desired value in a single shot.

ALTER SEQUENCE sequence_name
INCREMENT BY x
MINVALUE 0;

Where x is your desired increment (must be negative to go back).

Note: by running our check query, you will effectively lose the value returned. If you need a strict control over which key values are generated, you may try the solution at 2b to rewind the sequence and recover the used value.