-
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
-
Are you caching a part of your sequences? If so, this might be your problem.
-
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
-
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 [email protected]
Thanks
-
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
-
unix script
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
-
