Problems with Sequences

[Shyamis]

Hello Gang,

a quick question for you DB Gurus out there.

I exported an user from one instance and created a new instance and the user and imported the data back in. I noticed that the sequences were of lower values compared to the sequences in the original instance from where they were imported. It was not even the cached value that was missing, it was more than that. There was a difference of nearly 5000 when cache was just 20.

I have heard of similar problems before but generally I would have attributed it to cache. Can someone give a valid explanation please? Is there anyway that I get rid of this problem in the future (at the moment I wrote a procedure to check the max value in the table which uses the sequence and I just incremented... as in used nextval in a loop... till the sequence reached the value in the table)

thanks a million in advance
-Shyami

[prakashs43]

create script which collects the values of LAST_NUMBER of view DBA_SEQUENCES on source system( where Export is run)update sequence on target system ( where Import is run) to be equal to LAST_NUMBER so that application sees no gap.

[jovery]

What version of the DB are you running?

Also are you using the CONSISTENT=Y flag when you perform the export

Regards

[Shyamis]

I am running Oracle 8.0.6 and Yes, I am running Consistent=Y

Thanks for your help guys,
-Shyami

[jovery]

A couple of things..

Do the sequences exists in the target database, if they do you should drop them before the import or they will retain the original values.

Also was the export direct or conventinal path?

Regards

[Shyamis]

Jovery,

Actually the sequences did not exist in the target database. The
export was conventional path.

Thanks,
-Shyami