Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Exhaused sequence

Re: Exhaused sequence

From: Tim Gorman <>
Date: Thu, 17 Feb 2005 17:42:48 -0700
Message-ID: <>


Will they accept hex? :-) Just kidding...

It's Y2K all over again, eh?

With only 1,000,000 possible values (assuming 0 is included), how bad could it get? You can't beat it for simplicity, and what's the worst that could happen? A million failed tries at inserting a row? Of course, you'd want to make sure that it'll stop at some point and not keep circling endlessly...

The users will just have to be prepared for poor performance on inserts. Maybe that will help them make up their minds?

Another possible option (as opposed to changing the application code to use a stored function) is perhaps create a BEFORE INSERT FOR EACH ROW trigger on the table that will test the value being attempted, using a SELECT command. If that unique value already exists in the table, then have the trigger do the NEXTVAL->try/reject logic, so that the :NEW record will have the right value when the INSERT actually happens? Don't know if it'll work, but maybe it'll save you having to modify application code? That way, when they give the OK to expand the width of the column, you can just reset the sequence and get rid of the trigger, instead of doing another application code change.

...on the other hand, maybe hiding the problems from them is NOT the best way to handle this...? Let 'em savor the pain...

Best of luck!


on 2/17/05 2:55 PM, Knight, Jon at wrote:

> We have a sequence that is about to reach it's maximum value (999999).
> Ordinarly, I would just expand the column and let it keep going, but it
> populates a business column. They don't care what the value is, as long as
> it's unique and no longer than 6 digits. Of course, like many sequence
> populated columns, it's not consecutive.
> I'd like to go back and fill in those "gaps" while the business users
> decide what they want to do. So, I'm thinking: reset the sequence to zero &
> create a function that calls nextval until it finds one that's available.
> Besides a performance hit, are there any other gotchas I'm missing?
> Scalability?
> Has anyone done something similar before?
> Thanks,
> Jon Knight
> --

Received on Thu Feb 17 2005 - 19:45:46 CST

Original text of this message