Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Locked multiple sequencer hits.
OK, steve I read your other post too. Interesting requirements. Given
the situation you describe, At the beginning of your procedure, place a
For loop to execute 1,549,326 times. Put your nextval statement in it
but save only the first and last values to variables. You now have
your range of contiguous numbers. Put together your insert statements
using offsets into your range of numbers. I wonder though if you are
using the sequence correctly and/or you may want to revist your
database design.
> I'm inserting literally millions of rows. The Sequencer is set to
> increment by one - but since the rows are interdependent I need the
> id numbers BEFORE I insert. I want to make a call to pull off all
> 1,549,326 ids from a sequence at once. I also prefer the numbers
> to
> be sequential - and can not guarantee others will not try to
> access the
> sequencer. How do I do this.....
> I could do it with by locking the sequence table, altering the
> increment_by on the sequence, increment once and alter back.
> Problem, my process has privilege to lock the table, but not alter
> it.
> I'd rather not make 1,549,326 nextval calls and then do an equal
> number
> of inserts.
> How do I accomplish this. I'm new to the Oracle and db worlds -
> so I might
> not have some of my terminology right - please forgive.