Expdp fails with ORA-31693, ORA-01555

Sometimes, expdp export will fail with following error stack.

ORA-31693: Table data object "SCOTT"."TAB5" failed to load/unload 
and is being skipped due to error: 
ORA-02354: error in exporting/importing data 
ORA-01555: snapshot too old: rollback segment number 7 with name
 "_SYSSMU7_601426619$" too small

Normally this error is seen in two scenarios.

  •  LOB corruption
  •  Export when LOB handlers are still open

To check the problem, we can use the script provided by metalink [ID 787004.1]

After using the script, corrupt_lobs (or table used in the script) will have the ROWID’s of problem rows.

SQL> select count(*) from  SCOTT.corrupt_lobs;   

SQL> select * from   SCOTT.corrupt_lobs; 

Now to check if these rows are corrupt or the LOB’s are erroring out due to open handlers, we can use expdp to export rowid’s returned in above select .

expdp  directory=SCOTT_DATAPUMP dumpfile=test_blob_corr.dmp 
logfile=test_blob_corr.log tables=SCOTT.TAB5 
query=\"where rowid in \(\'AAAbMPAAIAAL5xzAAN\',
Export: Release - Production on Wed Jul 18 03:34:37 2012 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  
All rights reserved. Username: / as sysdba Connected to: 
Oracle Database 11g Enterprise Edition Release - 
64bit Production With the Partitioning,
Automatic Storage Management, OLAP, 
Data Mining and Real Application Testing options 
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA 
directory=SCOTT_DATAPUMP dumpfile=test_blob_corr.dmp 
logfile=test_blob_corr.log tables=SCOTT.TAB5 query="where 

Estimate in progress using BLOCKS method... 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA 
Total estimation using BLOCKS method: 61.02 GB Processing 
object type TABLE_EXPORT/TABLE/TABLE Processing object type 
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX 
. . exported "SCOTT"."TAB5"                         196.4 KB       
2 rows Master table "SYS"."SYS_EXPORT_TABLE_01" 
successfully loaded/unloaded 
 Dump file set for SYS.SYS_EXPORT_TABLE_01 is:  

If the rows are successfully exported, then there is no corruption.

But if they can’t be exported, then we either need to empty LOB column or delete the row. Later we can re-insert from backup.

NOTE: Before deleting or emptying the LOB, we should run the script in metalink note couple of time.  Each time it should return same ROWID’s.

This entry was posted in Oracle Data Pump and tagged , . Bookmark the permalink.

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