I have six tables that all use one auto incrementing sequence in Oracle. Problem is, every time the database is redeployed, the next value in the sequence is incorrect because it has to be set statically. So I found this sql on the internet which will update your sequence number according to the next unused ID from your table. I modified the sql because my sequence spanned more than one table. So the modified sql uses a temp table to find the next unused id across multiple tables.
It runs only in sqlplus (due to the use of the variables). I run it with this command:
My modified sql.
UPDATE: Thanks to Dan Wilson, Business Analyst where I am currently under contract, for giving me some sql to handle this much better than the sql I had previously found.
SELECT 'DROP SEQUENCE REPORT_LOG_SEQ ; CREATE SEQUENCE REPORT_LOG_SEQ INCREMENT BY 1 START WITH ' || NVL((SELECT TO_CHAR(MAX(LOG_ID)+1) FROM REPORT_LOG),20000) || ' MAXVALUE 1.0E27 MINVALUE 1 NOCYCLE CACHE 20 NOORDER ;' FROM DUAL
This code drops the sequence and then recreates it using the highest id currrently in use in a table, plus one. The only caution about this method, is that, if like my situation, you are using a sequence for multiple tables, then you have to use the table with the highest id in it. So if you are in that situation and you need to automate this process, and you won’t know which table has the highest id, then you may still have to use the previous method I used above. Otherwise, this is much simpler.