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

On Mar 29, 8:03*pm, sankarKK <xta... (AT) gmail (DOT) com> wrote:
SQL> DESC dba_sequences;

Name
Null? Type
-----------------------------------------------------------------
-------- --------------------

SEQUENCE_OWNER NOT
NULL VARCHAR2(30)
SEQUENCE_NAME NOT
NULL VARCHAR2(30)
MIN_VALUE
NUMBER
MAX_VALUE
NUMBER
INCREMENT_BY NOT
NULL NUMBER
CYCLE_FLAG
VARCHAR2(1)
ORDER_FLAG
VARCHAR2(1)
CACHE_SIZE NOT
NULL NUMBER
LAST_NUMBER NOT
NULL NUMBER

So:

SELECT 'CREATE SEQUENCE '||sequence_name||' '
WHERE sequence_owner='&Sequence_Owner';

HTH
-g

On Mar 29, 3:03*pm, sankarKK <xta... (AT) gmail (DOT) com> wrote:
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 --