What is the Maximum Sequence number value?

[tpbjr]

Hi,

I am concerned about reaching the maximum sequence number on a table.

What is the maximum number?
Does it roll-over and start at zero or one?
Will any problems occur when and if a roll-over occur?


Thank you for all your help

http://www.besware.com

Tom

 

[SantaMufasa]

Tom,

In Oracle 9, the limit for sequences is 28 digits. So, yes, you should be concerned about reaching the maximum of a sequence, but no more concerned than being hit by a meteor .

Happy holidays,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 19:45 (15Dec04) UTC (aka "GMT" and "Zulu"),
@ 12:45 (15Dec04) Mountain Time

 

[hoinz]

Tom, Dave,

I don't want to be pedantic,
but I think we cannot take it for granted that all sequences behave like Dave explained.
They may have been created by someone else, a long time ago, in an older version of Oracle. They might not make use of all those 28 digits.

Tom,
just to be sure, I suggest you have a look at view user_sequences (or dba_sequences or all_sequences).
Something like this:
select sequence_name, min_value, max_value, last_number from user_sequences;
You may have to use column command before this, to get a readable format for those possibly huge numbers in sqlplus.

regards

 

[sem]

Each sequence is defined by min_value, max_value, cycle_flag and order_flag parameters. You may get specific values from [ALL|USER|DBA]_SEQUENCES views. I think the names are self-descriptive.

Regards, Dima

http://seminihin.narod.ru

 

[carp]

So the specific answers to the original post are:

1. I am concerned about reaching the maximum sequence number on a table.
Sequences are not on tables. They are independent objects (which may be used to populate columns in a table).

2. What is the maximum number?
It depends. If it was created with default values, it's very, very large. Regardless of how it was created/altered, you can look in the dba/all/user_sequences view and see what the maximum value is.

3. Does it roll-over and start at zero or one?
It depends on how it was created/altered. Again, you can look in the data dictionary to find specifics.

4. Will any problems occur when and if a roll-over occur?
It depends. What are you using it for? If it is to populate a column with a primary or unique key constraint, then yes, it will create a problem. If it is to determine the next customer to serve, then no, it probably does not matter (think about the last time you were at a delicatessan, DMV, or anywhere else where they have you take a number and wait your turn. Does the rollover hurt? No. On the other hand, think about your driver's license - will the rollover hurt? Probably.).

 

[SantaMufasa]

Frankly, Hoinz, I love being pedantic...it's about the only exercise I get. And yes, in a galaxy far, far away, using a version of Oracle long, long gone, there may have been a smaller maximum on sequences than 28 digits, but such users probably knew my great-grandfather personally.

But you are absolutely correct: One must look at all values (in DBA_, ALL_, or USER_SEQUENCES and in any Forms and other applications) relating to a sequence's behaviour to gain a comfort level with the risks and limitations that that sequence exhibits. (For example, the pool of available sequence numbers diminishes when the sequence's "INCREMENT BY" = 100000000...but who lives that way?)

But all excellent points by Hoinz, Sem, and Carp.

Happy holidays to all !

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:25 (16Dec04) UTC (aka "GMT" and "Zulu"),
@ 10:25 (16Dec04) Mountain Time

 

[carp]

And a Merry Christmas to you, Dave - although I'm sure we'll cross trails here many more times between now and then!