Recreate sequences after inconsistent export

Imported a data pump export (.dmp) but then noticed that the sequences were not correct (en español.)

select ord_seq.nextval from dual;
–25

select max(ord_id) from orders;
–138

And the users were getting these errors: ORA-00001: unique constraint (SALES.PK_ORDERS) violated.

Apparently, sequences get skipped if they are being used while the export takes place (see here.)

To fix this, I had to rebuild the sequences.  I exported the create sequence statements to a .sql file, dropped the existing sequences, and then ran the .sql file in the database:

#######################
# Import for oradb
#######################

ORACLE_SID=oradb
ORACLE_HOME=/u01/app/oracle/product/11.2.03/db_1

#dumpfile=oradb_13_12_01_full_01.dmp
#backup_dir= /backups
$ORACLE_HOME/bin/sqlplus -s ‘/ as sysdba’ <<EOF
set pages 0 echo off feed off
CREATE or REPLACE DIRECTORY bkp_dir as ‘/backups’;
GRANT READ, WRITE ON DIRECTORY bkp_dir to SYSTEM;
quit;
EOF

$ORACLE_HOME/bin/impdp system/*** directory=bkp_dir dumpfile=oradb_13_12_01_full_%U.dmp logfile=oradb_impseq.log sqlfile=oradb_seq.sql include=sequence

This script will generate an oradb_seq.sql script in the bkp_dir location.  Before running it, I dropped the existing sequences first; this script will generate the drop statements:

SELECT ‘DROP ‘ || OBJECT_TYPE || ‘ ‘ || OWNER || ‘.’ || OBJECT_NAME || ‘;’ FROM DBA_OBJECTS
where 
owner in (<list of owners, in single ‘quotations’, separated by commas)

and object_type = ‘SEQUENCE’ order by owner, object_name;


After the sequences are dropped, run the script to recreate them:
sqlplus / as sysdba
SQL>@oradb_seq.sql

To avoid inconsistent exports, use the flashback_scn parameter in the export.  For an export script that grabs the SCN automatically, click here.


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s