Chyba 1.
ORA-31693: Table data object "USER1"."DOC" 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_2022816202$" too small
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", 2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", 3 ROUND((d.undo_size / (to_number(f.value) * 4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" 5 FROM ( 6 SELECT SUM(a.bytes) undo_size 7 FROM v$datafile a, 8 v$tablespace b, 9 dba_tablespaces c 10 WHERE c.contents = 'UNDO' 11 AND c.status = 'ONLINE' 12 AND b.name = c.tablespace_name 13 AND a.ts# = b.ts# 14 ) d, 15 v$parameter e, 16 v$parameter f, 17 ( 18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) 19 undo_block_per_sec 20 FROM v$undostat 21 ) g 22 WHERE e.name = 'undo_retention' 23 AND f.name = 'db_block_size';
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec] ------------------------------------------------------------------------------- 940 1092 7101
Řešení
Zvětšit UNDO a UNDO_RETENTION
SQL> alter database datafile 'H:\ORACLE\ORADATA\PROD\DATAFILES\UNDOTBS01.DBF' autoextend on maxsize 30G; Database altered. Nebo SQL> ALTER DATABASE DATAFILE 'H:\ORACLE\ORADATA\PROD\DATAFILES\UNDOTBS01.DBF' RESIZE 8G; Database altered.
SQL> ALTER SYSTEM SET UNDO_RETENTION = 8500; System altered.
Chyba 2.
ORA-31693: Table data object "USER1"."DOC" 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
Řešení
Tento problém se týká LOB segmentu, neboť LOB defaultně nevyužívají UNDO tablespace, ale ukládají změněné bloky v daném tablespace.
SQL> desc USER1.DOC Name Null? Type ----------------------------------------- -------- ---------------------------- ID NOT NULL NUMBER CURRENTSTEPSEQUENCENO NOT NULL NUMBER PROPERTIES BLOB REFERENCEID NUMBER LASTUPDATEON DATE LASTUPDATEDBY NVARCHAR2(50) CREATEDON DATE
Na MOS jsou k tomu dokumenty
Export Fails With Errors ORA-2354 ORA-1555 ORA-22924 And How To Confirm LOB Segment Corruption Using Export Utility? (Doc ID 833635.1)
Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
Řešení 1.
Můžete zkusit nastavit PCTVERSION pro daný LOB na vyšší hodnotu než je default 10%
SQL> select column_name, nvl(retention, 0) retention, pctversion from dba_lobs where table_name = 'DOC';
COLUMN_NAME -------------------------------------------------------------------------------- RETENTION PCTVERSION ---------- ---------- PROPERTIES 0 10 SQL> ALTER TABLE USER1.DOC MODIFY LOB (PROPERTIES) (PCTVERSION 30); Table altered.
Řešení 2.
Nastavte LOB, aby využíval UNDO.
SQL> ALTER TABLE user1.DOC MODIFY LOB (PROPERTIES) (RETENTION);
Table altered.
SQL> select column_name, nvl(retention, 0) retention, pctversion from dba_lobs where table_name = 'DOC';
COLUMN_NAME -------------------------------------------------------------------------------- RETENTION PCTVERSION ---------- ---------- PROPERTIES 8500 PROPERTIES 0 10