Datapump export failed with ORA-01555 on table with LOB

ERROR: 
RA-31693: Table data object "OWNER."DOC_GRP_BLOB" 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 11 with name "_SYSSMU11_583061684$" too small

Cause:


The LOB Retention is not defined properly.
This is confirmed by the queries:

SYS@xx> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SYS@xx> select retention, pctversion from dba_lobs table_name='DOC_GRP_BLOB';

no rows selected

The LOB Retention is not defined properly.
SYS@xx> alter system set undo_retention = 2400 scope = BOTH;

System altered.

SYS@xx> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 2400
undo_tablespace string UNDOTBS1

SYS@xx> ALTER TABLE ITC_OAS.DOC_GRP_BLOB MODIFY LOB (DOC_BLOB) (PCTVERSION 10);
Table altered.
SYS@xx> ALTER TABLE itc_oas.DOC_GRP_BLOB MODIFY LOB (DOC_BLOB) (RETENTION);
Table altered.
SYS@xx> select retention, PCTVERSION from dba_lobs where table_name='DOC_GRP_BLOB';

RETENTION PCTVERSION
---------- ----------
10
2400




No comments:

Post a Comment