Re: re-numbering pimary-key

Michael Hill <hillmw_at_ram.lmtas.lmco.com> wrote in message news:<3FDE299C.9C38C69E_at_ram.lmtas.lmco.com>...
> I have a series of tables that have primary-key / foreign key
> relationships where the primary key was being incremented in error by 20
> instead of by 1. The default cache value was set to 20. It has since
> been re-set to 1.
>
> I'd like to re-number the primary key and then re-set the sequence then
> back to the correct number to increment.
>
> Anyone have any lessons leaned that I need to watch in doing this?
>
> Example of proposed numbering change:
> If I have 1,4,8,12,18
>
> I would change:
> 18 to 99,
> 12, to 98.
>
> Then change
> 4 to 2,
> 8 to 3,
> 98 to 4,
> 99 to 5.
>
> Then change the next sequence number to 6.
>
> Anyone see any problem with this?
>
> Mike

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.

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 - 16:43:37 CET