Snapshot too old from READ-ONLY table (data pump export)

From: Brad Peek <brad_peek_at_yahoo.com>
Date: Fri, 15 Jul 2011 10:24:59 -0700 (PDT)
Message-ID: <1310750699.51520.YahooMailNeo_at_web112909.mail.gq1.yahoo.com>


A table level export (via data pump)�is failing with an ORA-01555.��� The curious thing (to me, anyway) about this is that the tablespace containing the table is set to READ-ONLY so I don't get what would be needed from the UNDO segments anyway.���
�
Before I open up an SR (SR = serious run-around), can someone help me out with why export might need to get a "before" image of a block that hasn't changed?���� The export is taking much longer than I would have expected (> 10 hours before failing) so there may be multiple issues at play here.
�
Could it�be the datapump master table that is getting the ORA-01555 (seems unlikely)?��If so,�how would I confirm that, and how would I get around that issue even if that is the case?
�
This error is repeatable.� I first got this error trying to export the whole schema, so I decided to try a single table export (details below).�� 
�
From the export log:
--------------------
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":� system/******** parfile=expdp_mic_air_sp_send.parfile
Estimate in progress using STATISTICS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
.� estimated "MIC_AIR"."SP_SEND"������������������������ 119.5 GB
Total estimation using STATISTICS method: 119.5 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MIC_AIR"."SP_SEND" 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 46 with name "_SYSSMU46_1630464369$" too small
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
� /u01/app/datapump_dir/expdp_mic_air_sp_send_01.dmp
� /u01/app/datapump_dir/expdp_mic_air_sp_send_02.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 06:09:00

The parameter file is:
---------------------
$ cat expdp_mic_air_sp_send.parfile
tables=mic_air.sp_send
directory=data_pump_dir
dumpfile=expdp_mic_air_sp_send_%U.dmp�� 
filesize=10737418240������������������� 
logfile=expdp_mic_air_sp_send.log
estimate=statistics
compression=all

From the instance ALERT LOG:
---------------------------------
Fri Jul 15 06:08:47 2011
ORA-01555 caused by SQL statement below (SQL ID: 1sqssb6vhvr6r, SCN: 0x000c.05716ebf):
SELECT * FROM RELATIONAL("MIC_AIR"."SP_SEND")

Note that in the above output from the instance alert log, the statement is referencing a function named RELATIONAL.��� That could be a clue, but I don't think I have seen that before (must be a data pump thing).
�
The table doesn't contain any LOB columns:
--------------------------------------------
BPEEK_at_shareprd1> desc mic_air.sp_send
�Name������������������������������������� Null?��� Type
�----------------------------------------- -------- ----------------------------
�OID_SP_SEND����������������� NOT NULL NUMBER(10)
�MAILING_ID���������������������������������������� VARCHAR2(20)
�RECIPIENT_TYPE�������������������������������� VARCHAR2(20)
�CONTACT_SOURCE��������������������������� NUMBER(10)
�REFERENCE_NUM������������������������������� VARCHAR2(11)
�EMAIL_ADDRESS��������������������������������� VARCHAR2(80)
�EVENT_TYPE���������������������������������������� VARCHAR2(20)
�WHEN_SENT����������������������������������������� DATE
�CAMPAIGNID���������������������������������������� NUMBER(15)
�OFFERID������������������������������ ������������������ NUMBER(15)
�MAILING_NAME����������������������������������� VARCHAR2(120)
�SUBJECT_LINE�������������������������������������� VARCHAR2(120)
�OID_CUSTOMER����������������������������������� NUMBER(10)
�ADDUID����������������������������������� NOT NULL VARCHAR2(8)
�WHEN_ADDED������������������������������� NOT NULL DATE
�LASTUID���������������������������������� NOT NULL VARCHAR2(8)
�WHENLASTUPDATE��������������������������� NOT NULL DATE
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 15 2011 - 12:24:59 CDT

Original text of this message