Sequence number shows a lower value after dump file import

Question

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.

Answer 1

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.

Answer 2

You can drop and create SEQ again:

Example:
DB1: DB export
DB2: DB import

1. select list drop SEQ from DB2, after that, drop SEQ with list drop in DB2

SELECT
'DROP ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ';' "DROP SEQUENCE"
FROM DBA_OBJECTS
where owner in ('USER_1')
and object_type = 'SEQUENCE'
order by owner, object_name;

2. select list create SEQ from DB1, create SEQ with list create in DB2

select trim(DBMS_METADATA.GET_DDL('SEQUENCE',a.OBJECT_NAME, a.OWNER)) "SEQUENCE_DDL"
from dba_objects a
where a.OBJECT_TYPE ='SEQUENCE'
and a.OWNER in ('USER_1')
order by a.OWNER, a.OBJECT_NAME;