EXPDP ORA-31693 ORA-02354 ORA-01555

while taking an export  (my Oracle Release is 11.2.0.3), i have faced with the below errors in the log file:

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object “TESTSCHMA”.”VALUE_TABLE” 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 9 with name “_SYSSMU9_2795927347$” too small

To Resolve this problem perform the following:

sqlplus

SQL>set pages 1000
SQL>set lines 300
SQL> show parameter undo;

NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
increase the undo_retention size:

SQL> alter system set undo_retention = 16500 scope = BOTH;

The LOB “retention” will not change automatically for LOB segements to verify this use the below query:

select table_name, column_name, nvl(retention, 0) retention, pctversion from dba_lobs where OWNER=’TESTSCHMA’;

To find all columns that have lobs use the following query:

select table_name, column_name from dba_lobs where OWNER=’TESTSCHMA’;

Then, use the “alter” command to change invidually each lob:

SQL> alter table TESTSCHMA.GRID_TABLE modify lob (SQLSTATEMENT) (retention);

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s