Re: re-numbering pimary-key

> I just tried to change some in test and of course it wouldn't let me change any because of the
> constraints. I was afraid of that.
 

>
> Mike, I have actually gone through and migrated all related rows in an
> application where the parent table key was a sequence value.
> Logically it was not that hard to write the pl/sql code I used to
> perform the processing but it was a fairly expensive process from the
> point of view of the work that has to be done by Oracle, indexes
> updated, redo generated, etc....
>
> Here is an alternate that we are about to do today. Due to a sequence
> that is about to recycle (due to size limitation of external system we
> feed) and where we must first remove existing data to allow the reuse
> of the sequence value, but where the customer does not want any data
> removed until after year-end since this will distort their
> year-to-date information, is to substitute our own home-grown
> sequence.
>

So all you are doing is re-using the holes that exist. Do you have an example of that code. This may be a better idea.

>
> What I did was write a function as an anonymous transaction that reads
> one row from an IOT that was populated with the missing (skipped)
> sequence numbers using select for update, delete, commit, return the
> selected value.
>
> It seems to test just fine. As long as you are on Oracle version 8i
> and the function is not called as part of a distributed transaction
> this may be a simplier route to take. With version 9 the distributed
> transaction restriction should be removed. We just substituted our
> function name for the sequence, recompiled the four programs that call
> the sequence, and tested.
>
> HTH -- Mark D Powell --
  Received on Tue Dec 16 2003 - 18:03:03 CET