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:
sqlplus user/pass@/path/scriptname
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.
Comment
Subscribe
Stumble
Buzz
Previous
3 Responses to Updating Oracle Sequences to the Next Highest Unused ID
Curt Sampson
September 13th, 2005 at 9:38 am
Oh, the humanity! This alone would make it worth switching from Oracle to PostgreSQL, where you say:
SELECT setval(‘report_report_id_seq’, coalesce((SELECT max(report_id) FROM report), 1))
Tod McQuillin
September 13th, 2005 at 10:37 am
The use of a temporary table is not required. You can get the same result with a query like this:
select max(id) from (
select report_id as id from report
union select chart_id as id from report_chart
union select datasource_id as id from report_datasource
union select group_id as id from report_group
union select parameter_id as id from report_parameter
union select reportuser_id as id from report_user
)
Simon Huang
August 9th, 2008 at 3:19 am
Thank you very much! But when I use that sql, that is “SELECT ‘DROP SEQUENCE REPORT_LOG_SEQ ; …”, in a trigger’s body which will be executed after insert, I received an error like this: “ORA-04091: table XXXX.AX_POLICY is mutating, trigger/function may not see
it”.
So I prefered sql like this:
create or replace trigger inc_ax_policy_id_specified
before insert on ax_policy
for each row when (new.policy_id is not null)
declare
current_val number:=0;
inserted_val number:=:new.policy_id;
begin
loop
exit when (current_val > inserted_val);
select ax_policy_seq.nextval into current_val from dual;
end Loop;
end;
/
And there was already a trigger like this:
create or replace trigger inc_ax_policy
before insert on ax_policy
for each row when (new.policy_id is null)
begin
select ax_policy_seq.nextval into :new.policy_id from dual;
end;
/
These triggers just simulate auto increase function in MySQL, and looks like they work well in my situation.