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,