Wednesday, September 7, 2016

ORA-01555: snapshot too old: rollback segment number


SCHEMA: WMPYMYISADMIN82
TABLE: BIZDOCCONTENT

ORA-31693: Table data object "WMPYMYISADMIN82"."BIZDOCCONTENT" 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 15 with name "_SYSSMU15_134866855$" too small

---------------------------------------------------------------------------------------------------------------------------------
SQL> alter system set undo_retention=88888888 scope=both;

SQL> show parameter undo_retention

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_retention                       integer                           88888888

select owner,table_name, column_name, nvl(retention, 0) retention, pctversion from dba_lobs where owner = 'WMPYMYISADMIN82'
and table_name='BIZDOCCONTENT';

OWNER                          TABLE_NAME                     COLUMN_NAME       RETENTION PCTVERSION
------------------------------ ------------------------------ ----------------- ---------- ----------
WMPYMYISADMIN82                BIZDOCCONTENT                  CONTENT           900

alter table WMPYMYISADMIN82.BIZDOCCONTENT modify lob (CONTENT) (pctversion 5);
alter table WMPYMYISADMIN82.BIZDOCCONTENT modify lob (CONTENT) (retention);

OWNER                          TABLE_NAME                     COLUMN_NAME        RETENTION PCTVERSION
------------------------------ ------------------------------ ------------------ ---------- ----------
WMPYMYISADMIN82                BIZDOCCONTENT                  CONTENT            88888888

----------------------------------------------------------------------------------------------------------------------------------

SCHEMA: WMPYMYISADMIN82
TABLE: UTSCHS

ORA-31693: Table data object "UNILAB"."UTSCHS" 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 8 with name "_SYSSMU8$" too small

---------------------------------------------------------------------------------------------------------------------------
SQL> alter system set undo_retention=88888888 scope=both;

SQL> show parameter undo_retention

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
undo_retention                       integer                           88888888


select owner,table_name, column_name, nvl(retention, 0) retention, pctversion from dba_lobs where owner = 'UNILAB'
and table_name='UTSCHS';