Oracle export failed: EXP-00006: internal inconsistency error

Question

My call to Oracle's exp is failing with:

EXP-00006: internal inconsistency error
EXP-00000: Export terminated unsuccessfully

The command I execute is

C:\oracle\product\11.1.0\db_1\BIN\exp user/pass@instance file=dump.dmp log=log.log compress=y owner=user

The table that is failing is contains

• 1 not null number(38) columns (primary key)
• 18 varchar2(100) columns

The primary key is also a foreign key to another table.

All search results say that I should contact Oracle, but isn't there someother fix for this problem? Maybe a rebuild of a table?

Answer 1

If you have Oracle Support, then tell them. It can be an internal problem with export itself and they will request additional information, tell you what to try and so on, until you resolve the problem. You will probably also get a patch/fix if there is.

I would suggest doing the export with datapump instead as export is long time deprecated. You need to define a directory where oracle should drop exported data into, then start datapump. It's easy, faster and more reliable and flexible than import/export.

Example:

SQL> CREATE OR REPLACE DIRECTORY expdump AS '/export/home/oracle/expdump';

SQL> GRANT READ, WRITE ON DIRECTORY expdump TO user_who_will_export;

Then run:

$ expdp user/pass schemas=schema1,schema2 directory=expdump dumpfile=myexport.dmp logfile=myexport.log

Addition: Looks like you have problems with consistency of the DB. Run logical and physical validation with RMAN to catch any other problems:

RMAN> connect target /

RMAN> run {
# set disk to be default device type
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
# number of concurrent sessions to spawn (if you can affor parallelism)
CONFIGURE DEVICE TYPE DISK PARALLELISM 10 BACKUP TYPE TO BACKUPSET;
# check datafiles for corruption 10 datafiles in each session (if you can afford paralellism)
BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;
}

Code is generic, this snippet borrowed from http://oraclespin.wordpress.com/2008/06/11/how-to-check-physical-and-logical-data-corruption-using-rman/

Answer 2

run : analyze table validate structure cascade;

See if it returns an error that something is bad. If its an index that is corrupt, drop and recreate, if its something else attempt to repair the table if you can/know how. Get oracle support to help if you need.

Answer 3

I fixed this one issue by determining which table the cause of the problem is. I then copied that table to a new one :

create table X as Select * from Y

after that I dropped the old table and renamed the new table. I created the constraints and everything was fine.