I'm using Oracle 11g database in which I have more than 12 schema's consisting of many tables. I've created sequences for primary keys to these tables. I've also created synonyms to refer my application from the main schema.

My issue is, while i try to export(using expdp) the schema's from DB in 1 server and import(impdp) the dump file to db installed in another server, sequence values only for some tables are getting changed. Even if i try to remap the table space or schemas, the value of sequence shows a lesser value than rows in their respective tables. Due to which when i access the application it throws an error while saving the data.

I am confused and don't getting an idea to solve this. Please advise. Is their any method to solve this issue?

Thanks in advance.

  • Also, while took my DB export it shows "Total estimation using BLOCKS method: 20.83 GB" But my DB export dump is only 5 GB. Few days back we deleted some unwanted files and data from our database. But I not getting this huge difference. But export dump file is not showing any error.
    – SHS
    Commented Jan 27, 2017 at 13:26
  • The estimate is just a (frequently wild) estimate. If you are compressing the export via DataPump, I think the estimate may not take that into account. And if you have deleted some database files and data, that might not be taken into account in the estimate. Generally the estimate is on the high side, which is good, so you can be fairly sure that you won't run out of disk space if you have 30 GB free and the estimate is 29 GB. Commented Dec 29, 2020 at 15:40

2 Answers 2


If you run export command against live database without parameters like flashback_scn or flashback_time which ensures consistency during the export, then this can be an expected behavior because these objects are exported in different time and one can become inconsistent with another.

For example, if the sequence object is exported before the table and the application has inserted new records using the sequence and exported later then table and sequence can be out of sync.

Stopping application before starting the export process or using expdpparameters like flashback_time(this may not export all the transactions made after the time specified in this parameter unless you stop the application) can prevent this situation. Or you can adjust the sequence value after the import by recreating it.


You can drop and create SEQ again:

DB1: DB export
DB2: DB import

  1. select list drop SEQ from DB2, after that, drop SEQ with list drop in DB2
'DROP ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ';' "DROP SEQUENCE"
where owner in ('USER_1')
and object_type = 'SEQUENCE'
order by owner, object_name;
  1. select list create SEQ from DB1, create SEQ with list create in DB2
from dba_objects a
and a.OWNER in ('USER_1')
order by a.OWNER, a.OBJECT_NAME;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.