ORA-31693, ORA-02354 and ORA-01555 with Export Datapump,ora-31693ora-02354

來源:互聯網
上載者:User

ORA-31693, ORA-02354 and ORA-01555 with Export Datapump,ora-31693ora-02354
Symptoms
ORA-31693: Table data object "YXFUND"."MF_NOTTEXTANNOUNCEMENT" 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 10 with name "_SYSSMU10$" too small 

cause

The old versions (consistent read) of the LOB can be specified by either the PCTVERSION or the RETENTION parameters.


For SecureFiles, only the RETENTION parameter can be specified.


for BasicFiles LOBs you can specify either PCTVERSION or RETENTION , but not both. 

- PCTVERSION : This parameter specifies the percentage of all used BasicFiles LOB data space that can be occupied by old versions of BasicFiles LOB data pages. Under 11g compatibility, this parameter is silently ignored when SecureFiles LOBs are created.

PCTVERSION is the default in manual undo mode and the default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space.

You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. 

- RETENTION is the default in automatic undo mode. 

You can specify the RETENTION parameter only if the database is running in automatic undo mode. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. In automatic undo mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION.

You can specify the optional settings after RETENTION only if you are using SecureFiles. 

You can see more details about the RETENTION parameter for SecureFiles and BasicFiles LOBs in the following link :

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45282
RETENTION Parameter for SecureFiles LOBs

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45281
RETENTION Parameter for BasicFiles LOBs


solution

SQL>show parameter undo


NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
_gc_undo_affinity                    boolean                           FALSE
undo_management                      string                            AUTO
undo_retention                       integer                           900
undo_tablespace                      string                            UNDOTBS2


SQL> select max(maxquerylen) from v$undostat;


MAX(MAXQUERYLEN)
----------------
            1785


SQL> select COLUMN_NAME,PCTVERSION,RETENTION from dba_lobs where OWNER='YXFUND' and TABLE_NAME='MF_NOTTEXTANNOUNCEMENT';


COLUMN_NAM PCTVERSION  RETENTION
---------- ---------- ----------
CONTENT            10


SQL>ALTER SYSTEM SET UNDO_RETENTION = 7200 scope=both sid='*';


SQL>show parameter undo



NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
_gc_undo_affinity                    boolean                           FALSE
undo_management                      string                            AUTO
undo_retention                       integer                           7200
undo_tablespace                      string                            UNDOTBS2




SQL> select COLUMN_NAME,PCTVERSION,RETENTION from dba_lobs where OWNER='YXFUND' and TABLE_NAME='MF_NOTTEXTANNOUNCEMENT';


COLUMN_NAM PCTVERSION  RETENTION
---------- ---------- ----------
CONTENT                     7200




SQL>alter table YXFUND.MF_NOTTEXTANNOUNCEMENT modify lob(CONTENT) (pctversion 5);


SQL>alter table YXFUND.MF_NOTTEXTANNOUNCEMENT modify lob(CONTENT) (retention);


參考my oracle support :ORA-31693, ORA-02354 and ORA-01555 with Export Datapump (文檔 ID 1580798.1)
有人碰到過這樣的情況?expdp大表報錯ORA-01555

這個錯誤是很早就publisher的。
主要是說在執行匯出或查詢某張表的時候,又有其它使用者對該表進行了行修改。
修改提交後,通常UNDO中會保留這些舊的資料,用來保證資料一致性讀。

如果UNDO保留時間到了,並且UNDO中無足夠空間,這些在UNDO中的舊資料就會被覆蓋。那些依靠這些資料的操作就無法獲得一致性讀,從而報錯:ORA-01555

建議修改undo_retention、undo tablespace size到合適的大小
或者選擇資料庫空閑時間段執行expdp作業

ps: 在oracle11g中,EM advice centre - undo advisor 可以評估undo資料表空間和undo retention如何設定
 

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

Tags Index: