0

if I use Oracle Sql Developer connected to 11gR2 and execute:

ALTER SEQUENCE myCounter INCREMENT BY 10;

Do I need to commit this statement. If I execute this statement and before hitting the green commit button the network connection is lost, is the sequence still incremented by 10 ?

I do not fully understand what Oracle writes in their documentation: When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back.

1 Answer 1

2

Your alter statement does not need to be committed because it is DDL rather than DML. Losing the connection won't have any effect. But it also won't in itself increment the sequence value at all.

What it does is modify the sequence definition so that next time, and every subsequent time, nextval is called the generated number will increment by 10 rather than whatever it was set to before (the default is 1).

So as a quick demo:

CREATE SEQUENCE myCounter;

Sequence MYCOUNTER created.

SELECT myCounter.nextval from dual;

   NEXTVAL
----------
         1

SELECT myCounter.nextval from dual;

   NEXTVAL
----------
         2

SELECT myCounter.nextval from dual;

   NEXTVAL
----------
         3

So the sequence is incremented by one each time, by default. Then after you alter it, each call to nextval increments by ten instead of one:

ALTER SEQUENCE myCounter INCREMENT BY 10;

Sequence MYCOUNTER altered.

SELECT myCounter.nextval from dual;

   NEXTVAL
----------
        13

SELECT myCounter.nextval from dual;

   NEXTVAL
----------
        23

The alter itself didn't use up or skip 10 values - otherwise the first nextval would have got 23 rather than 13. It only changed the definition.

The documentation you refer to is saying that if you roll back, you don't get any of the previously-issued numbers generated again:

ROLLBACK;

Rollback complete.

SELECT myCounter.nextval from dual;

   NEXTVAL
----------
        33

You get 33, not 23 again. A value is issued and then is no longer available for any other session, including your own, to get from another nextval call - regardless of rollback or commit, or whether the value is actually used for anything. (It may eventually be re-issued if the sequence is set to cycle, but it won't be by default).

Note also that sequence generation isn't gapless, and isn't meant to be, both because of how rollback is handled and how the cache mechanism works. The numbers also aren't always issued strictly in sequence - in a RAC environment each node has its own cache and will allocate from within those.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.