RE: Snapshot too old from READ-ONLY table (data pump export)
Date: Fri, 15 Jul 2011 13:59:18 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F74402D04_at_AAPQMAILBX02V.proque.st>
Hi Brad,
Here's Tom Kyte's discussion on ORA-1555 on objects in a read only tablespace: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:895410916429
Hope it helps,
-Mark
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Brad Peek
Sent: Friday, July 15, 2011 1:25 PM
To: Oracle-L List
Subject: Snapshot too old from READ-ONLY table (data pump export)
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 GBTotal 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<mailto: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-lReceived on Fri Jul 15 2011 - 12:59:18 CDT