problem with sequences

[Nugpot]

Hi,

I seem to run into the sequences problem quite often, I do a full export of the database(prod, 7.3.4) and then
import(fromuser touser)on the test database(8.1.7.1).
Everything completes succefully without any warnings, but my sequences are out of order and I end up dropping and recreating them every time. I have a lot of sequences and I don't want to do this everytime.
Am I doing something wrong during the import.

I drop all the objects related to the schema and then I do the import(fromuser touser) with ignore=y.

I would apprciate your input, thanks
vj

[Zaggy]

Are you caching a part of your sequences? If so, this might be your problem.

[jgmagnus]

normally, i just use a script that checks the nextval and then re-creates all the sequences. once you have the script going, then it's not such a pain in the arse.

- Magnus

[Nugpot]

Yes, I'm using cache 20. so, do I have to drop and recreate it everytime?

Magnus, can you share that script? if so, please email to nugpot77084@yahoo.com
Thanks

[sambavan]

Turn off that caching, that would do the trick. If you want to start from 1 then you would have to recreate your sequence.

Sam

[jgmagnus]

the script drops whatever you are creating beforehand so you won't attempt to create a sequence that already exists.
it's a unix script, but you can just change some of the parameters that get passed like TARGET (schema you are moving to) and SOURCE (schema you are copying):

spool /tmp/sequences.sql
select 'drop sequence ${TARGET}.'|| sequence_name ||';'||'
create sequence ${TARGET}.'|| sequence_name ||'
increment by 1 start with '|| (last_number + 1) ||
' minvalue '|| last_number ||' order;'
from dba_sequences
where sequence_owner = upper('${SOURCE}');
spool off


- magnus

[Nugpot]

Thank you guys!!