3rd try - problem with newsgoup server.
The problem with looping through your sequence is that it may take a long
time. The following script will generate a script to drop and recreate
sequences with the correct last number. Run it on your old DB, copy the
output and run that on the new one.
/* BEGIN SCRIPT */
DEFINE SUBST_OWNER = '<SCHEMA TO TREAT>' /* Replace with Schema name */
SELECT 'DROP SEQUENCE ' || sequence_owner || '.' || sequence_name || ';'
FROM sys.all_sequences
WHERE sequence_owner = '&&SUBST_OWNER'
/
SELECT 'CREATE SEQUENCE ' || sequence_owner || '.' || sequence_name || ' '
||
'MINVALUE ' || min_value || ' ' ||
'MAXVALUE ' || max_value || ' ' ||
'INCREMENT BY ' || increment_by || ' ' ||
DECODE (cycle_flag, 'Y', 'CYCLE', 'NOCYCLE') || ' ' ||
DECODE (order_flag, 'Y', 'ORDER', 'NOORDER') || ' ' ||
DECODE (cache_size, 0, 'NOCACHE', 'CACHE ' || cache_size) || ' '
||
'START WITH ' || last_number || ';'
FROM sys.all_sequences
WHERE sequence_owner = '&&SUBST_OWNER'
/
/* END SCRIPT */
"Frank" <franjoe_at_frisurf.no> wrote in message
news:fcjK6.5993$Ty6.138871_at_news1.oke.nextra.no...
> Hi!
>
> You could make a little PLSQL with a loop that picks sequence_n.NEXTVAL
> until some reasonalble condition is reached.
> If the exact value of the sequences are of interest (I hope not) creating
a
> script based on data dictionary/current values in the old database may be
> possible. Sadly not quite a "newbie task", but I haven't time to write one
> right now :-( If questionee post a request for it, I can give it a try
> tomorrow.
>
> Frank,
>
>
>
>
> Robin Boscia <rboscia_at_att.com> wrote in message
> news: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 Thu May 10 2001 - 02:24:57 CDT
