Re: Help in sequence reset

From: gazzag <justified_at_jamms.org>
Date: Wed, 30 Mar 2011 04:28:10 -0700 (PDT)
Message-ID: <c9de9079-94a5-4484-81f5-6ce1eb169072_at_32g2000vbe.googlegroups.com>



On Mar 29, 8: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

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||' '

||'START WITH '||TO_CHAR(last_number+100)||' '
||'INCREMENT BY '||increment_by||' '
||'CACHE '||cache_size||';'

FROM dba_sequences
WHERE sequence_owner='&Sequence_Owner';

HTH
-g Received on Wed Mar 30 2011 - 06:28:10 CDT

Original text of this message