1

I am new to Oracle and sequence. I created a sequence in Oracle, ( I am using Oracle 11g right now )

But I am afraid if the sequence will lose its next supposed incremented value when Oracle server is restarted or recovered from a failure?

e.g let currently sequence is holding 5

The next value it should give when sequence is accessed be 6

But will it get back to 0 when database is reset/restarted?

Amit
  • 13
  • 4
  • For future reference: Oracle 12c now supports Session Sequences (`create sequence myseq session`) which are private to each session (i.e. get reset automaticaly). Normal sequences, however, are persisted as per the other answers below. – Jeffrey Kemp Nov 26 '14 at 11:12
  • Thanks for the update Jeffrey. The only problem here is we are not able to switch to Oracle 12c right now because of the other potential users of our systems we are working on. We have to stick to 11g for now, and stick to the sequence solution. Thanks anyways. – Amit Nov 28 '14 at 06:41
  • I never recommended upgrading to 12c. I just added the comment because StackOverflow is for everyone (not just you), including people in the future who come looking for answers. – Jeffrey Kemp Nov 28 '14 at 06:46
  • Thanks for another good update Jeffrey Kemp. Yes Stackoverflow indeed is a very good platform to share and guide and it really answered the future referenced answers in the past.[link]http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle – Amit Nov 29 '14 at 16:17

2 Answers2

2

Don't worry, the sequence values is persisted and is kept across restarts.

However the next value could be higher than the value 6 you expect. This depends on the CACHE setting of the sequence. If you have created it with e.g. CACHE 20 the cached values are lost. In your example the next value could then be 20 after an instance restart. But it will never be 0 or less than the previous value (unless you have enabled the CYCLE attribute and already used all available numbers)

1

A sequence is considered data just like other data, so it'll be in synch with the data. If by "reset" you mean "restore" : in that case it may be reset to zero as well ... but alongside with all other data, to be accurate! If you restore to a point in time that the sequence did not exist, it's going to restore to exactly that situation : no sequence.

If by means of "reset" you mean "restart", there's no issue : everything remains. That is, all committed data remains in the database.

tvCa
  • 796
  • 6
  • 13