Manually inserting data in a table(s) with primary key populated with sequence

Question

I have a number of tables that use the trigger/sequence column to simulate auto_increment on their primary keys which has worked great for some time.

In order to speed the time necessary to perform regression testing against software that uses the db, I create control files using some sample data, and added running of these to the build process.

This change is causing most of the tests to crash though as the testing process installs the schema from scratch, and the sequences are returning values that already exist in the tables. Is there any way to programtically say "Update sequences to max value in column" or do I need to write out a whole script by hand that updates all these sequences, or can I/should I change the trigger that substitutes the null value for the sequence to some how check this (though I think this might cause the mutating table problem)?

Answer 1

As part of your schema rebuild, why not drop and recreate the sequence?

Answer 2

You can generate a script to create the sequences with the start values you need (based on their existing values)....

SELECT 'CREATE SEQUENCE '||sequence_name||' START WITH '||last_number||';' FROM ALL_SEQUENCES WHERE OWNER = your_schema

(If I understand the question correctly)

Answer 3

Here's a simple way to update a sequence value - in this case setting the sequence to 1000 if it is currently 50:

alter sequence MYSEQUENCE increment by 950 nocache;
select MYSEQUENCE_S.nextval from dual;
alter sequence MYSEQUENCE increment by 1;

Kudos to the creators of PL/SQL Developer for including this technique in their tool.