ORA-31693, ORA-02354 and ORA-01555 with Export Datapump, ora-31693ora-02354

ORA-31693, ORA-02354 and ORA-01555 with Export Datapump, ora-31693ora-02354
Symptoms
ORA-31693: Table data object "YXFUND". "MF_NOTTEXTANNOUNCEMENT" 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 10 with name "_ SYSSMU10 $" too small

Cause

The old versions (consistent read) of the LOB can be specified by either the PCTVERSION or the RETENTION parameters.


For SecureFiles, only the RETENTION parameter can be specified.


For BasicFiles LOBs you can specify either PCTVERSION or RETENTION, but not both.

-PCTVERSION: This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. under 11g compatibility, this parameter is silently ignored when SecureFiles LOBs are created.

PCTVERSION is the default in manual undo mode and the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.

You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode.

-RETENTION is the default in automatic undo mode.

You can specify the RETENTION parameter only if the database is running in automatic undo mode. oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. in automatic undo mode, RETENTION is the default value unless you specify PCTVERSION. you cannot specify both PCTVERSION and RETENTION.

You can specify the optional settings after RETENTION only if you are using SecureFiles.

You can see more details about the RETENTION parameter for SecureFiles and BasicFiles LOBs in the following link:

Http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45282
RETENTION Parameter for SecureFiles LOBs

Http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45281
RETENTION Parameter for BasicFiles LOBs


Solution

SQL> show parameter undo


NAME TYPE VALUE
---------------------------------------------------------------------------------------------------
_ Gc_undo_affinity boolean FALSE
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDOTBS2


SQL> select max (maxquerylen) from v $ undostat;


MAX (MAXQUERYLEN)
----------------
1785


SQL> select COLUMN_NAME, PCTVERSION, RETENTION from dba_lobs where OWNER = 'yxfund 'and TABLE_NAME = 'mf _ NOTTEXTANNOUNCEMENT ';


COLUMN_NAM PCTVERSION RETENTION
------------------------------
CONTENT 10


SQL> ALTER SYSTEM SET UNDO_RETENTION = 7200 scope = both sid = '*';


SQL> show parameter undo

NAME TYPE VALUE
---------------------------------------------------------------------------------------------------
_ Gc_undo_affinity boolean FALSE
Undo_management string AUTO
Undo_retention integer 7200
Undo_tablespace string UNDOTBS2




SQL> select COLUMN_NAME, PCTVERSION, RETENTION from dba_lobs where OWNER = 'yxfund 'and TABLE_NAME = 'mf _ NOTTEXTANNOUNCEMENT ';


COLUMN_NAM PCTVERSION RETENTION
------------------------------
CONTENT 7200




SQL> alter table YXFUND. MF_NOTTEXTANNOUNCEMENT modify lob (CONTENT) (pctversion 5 );


SQL> alter table YXFUND. MF_NOTTEXTANNOUNCEMENT modify lob (CONTENT) (retention );


See my oracle support: ORA-31693, ORA-02354 and ORA-01555 with Export Datapump (Document ID 1580798.1)
Someone has encountered such a situation? Expdp large table error ORA-01555

This error was published very early.
When exporting or querying a table, other users modify the table.
After the modification is submitted, the old data is usually retained in the UNDO to ensure consistent data reading.

If the UNDO retained time is reached and there is not enough space in the UNDO, the old data in the UNDO will be overwritten. Operations that rely on the data won't get consistent reads, and thus report an error: ORA-01555

We recommend that you change the size of undo_retention and undo tablespace to a proper size.
Or select the idle time range of the database to execute the expdp job.

Ps: In oracle11g, EM advice center-undo advisor can evaluate how undo tablespace and undo retention are set