Re: Help in sequence reset

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 30 Mar 2011 07:11:58 -0700 (PDT)
Message-ID: <2c36c79f-5c2c-41a1-9b39-ca257d82c7fe_at_p16g2000vbi.googlegroups.com>



On Mar 29, 3:03 pm, sankarKK <xta..._at_gmail.com> wrote:
> Hi,
>
> I need help in altering sequence dynamically.
>
> the pl/sql program should be able to do
>
> 1.  select sequence name owned by an user
> 2.  get the last number of the sequence.
> 3.  get the next value (nextval)
> 4.  alter the sequence so that nextval is lastnumber+100    for all
> the sequence selected.
>
> actually I have refreshed one testdb from prod data, now when
> application trying to insert data (in testdb) getting unique
> constraint violation message, I am altering the sequence some 50/100
> numbers and everything working fine.
>
> thanks in advance
> tcy

If you have restored data from production or another system to test then what you want to do is query the data for the max value in the column populated using the sequence generator and then using that value and the current value reset the sequence. Depending on the application involved and how long since the data was sync'd the value could be ten of thousands apart and just bumping the sequence 50 or 100 values may not be enough.

HTH -- Mark D Powell -- Received on Wed Mar 30 2011 - 09:11:58 CDT

Original text of this message