Version Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Hello,
Recently our development database was refreshed with the data from production, but the Sequence's weren't copied over.
So of course the Sequence numbers are very out of sync with the Primary Key's of the tables throwing Primary Key errors.
How can I find out the Sequence names, tables names, primary key columns, and max values for those primary key columns that have max primary key values that are higher than the nextval of their corresponding sequences?
So far the nextval of the sequence numbers have all been lower than the max value for the primary key's for the matching tables.
We've been using the method:
SELECT max(primary key column)
FROM table;
SELECT sequence_name.nextval FROM dual;
ALTER SEQUENCE sequence_name INCREMENT BY max primary key value - current sequence value + 1;
SELECT sequence_name.nextval FROM dual;
ALTER SEQUENCE sequence_name INCREMENT BY 1;
But this is done when a record is inserted into a table and the error is thrown.
So I'm hoping there's a much better way to fix this.
Please let me if there any any questions.
Thanks,
Joe