CODEWITHSUNDEEP
oracle-database
Amit
Amit

Reputation: 13

Do Oracle Sequence lost when database is reset or recovered from a failure?

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?

Upvotes: 1

Views: 1185

Answers (2)

tvCa
tvCa

Reputation: 816

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.

Upvotes: 1

user330315
user330315

Reputation:

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)

Upvotes: 2

Related Questions