6 Replies Latest reply: Aug 1, 2013 10:38 PM by user12195658 RSS

    ORA-02354: error in exporting/importing data, ORA-01555: snapshot too old: rollback segment number  with name "" too small

    user12195658

      Hi ,

      i am getting below error while taking expdp backup of blob table .

       

      ORA-31693: Table data object "HCLM_ADMIN"."SCAN_UPLOADEDFILES_TEMP" 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  with name "" too small

      ORA-22924: snapshot too old

      ORA-31693: Table data object "HCLM_ADMIN"."TPA_FAXWATCHER" 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  with name "" too small

      ORA-22924: snapshot too old

       

      We have already make undo retention to 50000 .table structure are :

      SQL> show parameter undo

       

       

      NAME                                     TYPE        VALUE

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

      undo_management                      string        AUTO

      undo_retention                       integer           50000

      undo_tablespace                      string          UNDOTBS1

       

      SQL> alter table hclm_admin.SCAN_UPLOADEDFILES_TEMP modify lob(FILE_BLOB) (RETENTION);

       

       

      Table altered.

       

       

       

      SQL> select column_name, pctversion, retention

      from dba_lobs where owner='HCLM_ADMIN' and table_name='SCAN_UPLOADEDFILES_TEMP';

       

       

       

      COLUMN_NAME

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

      PCTVERSION  RETENTION

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

      FILE_BLOB

                      50000

      SQL> alter table hclm_admin.TPA_FAXWATCHER modify lob( FILEDATA_BLOB) (RETENTION);

      Table altered.

      SQL> select column_name, pctversion, retention from dba_lobs where owner='HCLM_ADMIN' and table_name='SCAN_UPLOADEDFILES_TEMP';

      COLUMN_NAME    PCTVERSION  RETENTION

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

      FILE_BLOB

                      50000

       

       

       

      CREATE TABLE HCLM_ADMIN.TPA_FAXWATCHER

      (

        FILENAME_VAR       VARCHAR2(50 BYTE),

        CREATED_DATE_DTE   DATE,

        FILEPATH_VAR       VARCHAR2(100 BYTE),

        TIMESTAMP_DTE      DATE,

        FAXNO_VAR          VARCHAR2(15 BYTE),

        DEPARTMENT_VAR     VARCHAR2(50 BYTE),

        REQUESTTYPE_VAR    VARCHAR2(50 BYTE),

        TAGTO_VAR          VARCHAR2(50 BYTE),

        REMARK_VAR         VARCHAR2(1000 BYTE),

        DOCTYPE_VAR        VARCHAR2(50 BYTE),

        TAGTOVALUE_VAR     VARCHAR2(50 BYTE),

        DOCTYPE_OTHER_VAR  VARCHAR2(50 BYTE),

        HEGIC_NO_VAR       VARCHAR2(50 BYTE),

        RECORDNO_NUM       NUMBER                     NOT NULL,

        FILEDATA_BLOB      BLOB,

        FAXLOCKDATE_DTE    DATE,

        FAXLOCKSTATUS_VAR  NUMBER,

        FAXLOCKBYUSER_VAR  VARCHAR2(50 BYTE)

      )

      LOB (FILEDATA_BLOB) STORE AS (

        TABLESPACE HCLM_ALERTSVC

        ENABLE       STORAGE IN ROW

        CHUNK       8192

        RETENTION

        NOCACHE

        LOGGING

        INDEX       (

              TABLESPACE HCLM_ALERTSVC

              STORAGE    (

                          INITIAL          64K

                          NEXT             1M

                          MINEXTENTS       1

                          MAXEXTENTS       UNLIMITED

                          PCTINCREASE      0

                          BUFFER_POOL      DEFAULT

                         ))

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       ))

      TABLESPACE HCLM_ALERTSVC

      PCTUSED    0

      PCTFREE    10

      INITRANS   1

      MAXTRANS   255

      STORAGE    (

                  INITIAL          64K

                  NEXT             1M

                  MINEXTENTS       1

                  MAXEXTENTS       UNLIMITED

                  PCTINCREASE      0

                  BUFFER_POOL      DEFAULT

                 )

      LOGGING

      NOCOMPRESS

      NOCACHE

      NOPARALLEL

      MONITORING;

       

       

       

       

      ALTER TABLE HCLM_ADMIN.TPA_FAXWATCHER ADD (

        PRIMARY KEY

        (RECORDNO_NUM)

        USING INDEX

          TABLESPACE HCLM_ALERTSVC

          PCTFREE    10

          INITRANS   2

          MAXTRANS   255

          STORAGE    (

                      INITIAL          64K

                      NEXT             1M

                      MINEXTENTS       1

                      MAXEXTENTS       UNLIMITED

                      PCTINCREASE      0

                     ));

       

       

       

       

       

      CREATE TABLE HCLM_ADMIN.SCAN_UPLOADEDFILES_TEMP

      (

        TEMPID_NUM      NUMBER,

        SESSION_ID      VARCHAR2(200 BYTE),

        UPLOADFILE_NUM  NUMBER,

        DOCNO_NUM       NUMBER,

        SCANJOB_NUM     NUMBER,

        FILENAME_VAR    VARCHAR2(200 BYTE),

        FILETYPE_VAR    VARCHAR2(200 BYTE),

        FILE_BLOB       BLOB,

        FLAG            VARCHAR2(200 BYTE),

        USERID_NUM      NUMBER,

        CREATED_DATE    DATE

      )

      LOB (FILE_BLOB) STORE AS (

        TABLESPACE PHCLMDBTBS

        ENABLE       STORAGE IN ROW

        CHUNK       8192

        RETENTION

        NOCACHE

        LOGGING

        INDEX       (

              TABLESPACE PHCLMDBTBS

              STORAGE    (

                          INITIAL          64K

                          NEXT             1M

                          MINEXTENTS       1

                          MAXEXTENTS       UNLIMITED

                          PCTINCREASE      0

                          BUFFER_POOL      DEFAULT

                         ))

            STORAGE    (

                        INITIAL          64K

                        NEXT             1M

                        MINEXTENTS       1

                        MAXEXTENTS       UNLIMITED

                        PCTINCREASE      0

                        BUFFER_POOL      DEFAULT

                       ))

      TABLESPACE PHCLMDBTBS

      PCTUSED    0

      PCTFREE    10

      INITRANS   1

      MAXTRANS   255

      STORAGE    (

                  INITIAL          64K

                  NEXT             1M

                  MINEXTENTS       1

                  MAXEXTENTS       UNLIMITED

                  PCTINCREASE      0

                  BUFFER_POOL      DEFAULT

                 )

      LOGGING

      NOCOMPRESS

      NOCACHE

      NOPARALLEL

      MONITORING;

       

       

      regards,