CODEWITHSUNDEEP
oracle-databaserollbackdatabase-sequence
Ori Marko
Ori Marko

Reputation: 58792

Oracle - How to rollback sequences inside multiple inserts

I have a script (that can fail) with multiple inserts that include nextval as

insert into table a (id, value) (id_seq.nextval, 'value');
...

If script fails and I rollback the inserts, the sequence isn't rollback and multiple ids won't be in use

Is there a best practice to avoid "losing" ids from sequence on multiple inserts?

I found similar questions but in different context

Upvotes: 0

Views: 767

Answers (3)

Chrᴉz remembers Monica
Chrᴉz remembers Monica

Reputation: 1904

It is not possible to set the current value of an existing sequence. If you need to change the value(currentvalue) of a sequence you have to "increment it back" or recreate it.

"Incrementing back"(dirty workaround):

-- current value is 100
ALTER SEQUENCE seq_x  INCREMENT BY -25;--number of wrong increments or max(id)-seq_x.nextval in a procedure
SELECT seq_x.NEXTVAL FROM dual; -increments by 1 step, in this case -25
-- -> new current value is 75
ALTER SEQUENCE seq_x INCREMENT BY 1;

Recreate it:

DROP SEQUENCE seq_x;
CREATE SEQUENCE seq_x INCREMENT BY 1 START WITH 75;-- desired start value
-- You need to reassign privileges, if some were given

If you're using a auto increment column instead, you can set it to the highes value in the table by using

ALTER TABLE table_name MODIFY ID GENERATED ALWAYS AS IDENTITY (START WITH LIMIT VALUE);
-- customize as you need it, i.e. GENERATED BY DEFAULT instead of GENERATED ALWAYS

If you always need the last value for whatever reason, you have to select MAX(id) from the given table. But: Missing ids in a table dont hurt performance or anything else, it only hurts some ppl eyes :)

Upvotes: 1

Popeye
Popeye

Reputation: 35910

ROLLBACK works on DML. It removes the effect of any data changes in tables that happened in the current transaction.

ROLLBACK can not change the sequence value to its original value (value of the sequence when the transaction is started).

Anyway, Sequence is just an incremental number and you will not lose any precious data if it slips by few numbers, until and unless your project needs a continuous sequence and it is the strict requirement. If it is so, then you must find other workarounds.

You can not control the gaps in the sequence.

Cheers!!

Upvotes: 3

Needle file
Needle file

Reputation: 518

No way to auto rollback sequence. And as usual u don't need that.

Upvotes: 2

Related Questions