Re: sequence help newbie!!

From: Andrew Simkovsky <asimkovsky_at_msn.com>
Date: Tue, 8 May 2001 11:12:48 -0400
Message-ID: <ODv3gXD2AHA.291@cpmsnbbsa07>

Tables and sequences are completely independent objects. As a matter of fact, you could use a single sequence for several tables, on any column you want to, or no table at all.

To bump up all your sequences all at once, you will have to do a little dynamic sql. Run this command and spool it to a file:

select

    'alter sequence '||owner||'.'||sequence_name||chr(10)||
    'increment by 300;'||chr(10)||
    'select '||owner||'.'||sequence_name||'.nextval from dual;'||chr(10)||
    'alter sequence '||owner||'.'||sequence_name||' increment by

1;'||chr(10)||chr(10)
from dba_sequences
where owner not in ('SYS','SYSTEM');

The file that you spool it to will be a script that you can run to bump up all your sequences. You can change the INCREMENT BY to any number you want to.

Andrew Simkovsky
OCP DBA
Pipeline SYSOP
www.revealnet.com

Robin Boscia wrote in message <9da0u8$2k13_at_kcweb01.netnews.att.com>...
>I am a newbie
>
>We are doing an export and an import, upgrading from 7.3.4 to 8.1.6, also
>upgrading the O.S. and moving all the data to a new machine.
>
>We have everything up and working, except our sequences.
>
>We can see them and they look the same as on the old machine, however when
>we try to open a new ticket, it does not increment properly. It starts
 back
>at number 1.
>
>We have over 220 sequences and do not really want to drop and recreate all
>of them.
>
>Is there an easy way to get your sequences "working"? without going thru
>massive amounts of scripts?
>Can you put something in the export or the import file to increment
>properly?
>
>
>Also when you query your sequences, (we did not build the database or the
>tables), we can see the sequence and it's associated table, but how do you
>know what column that sequence would apply to (for incrementation )?
>
>
>Thanks
>Robin
>
>
Received on Tue May 08 2001 - 10:12:48 CDT