Exhaused sequence

  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

--
http://www.freelists.org/webpage/oracle-l