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

Hi,

I've experienced the same issue a few years ago with a data pump ORA-1555 and 
the cause was delayed block cleanout. I did a little write up at the time which 
I've included as an attachement.

Chris




________________________________
From: Brad Peek <brad_peek_at_yahoo.com>
To: "Bobak, Mark" <Mark.Bobak_at_proquest.com>
Cc: Oracle-L List <oracle-l_at_freelists.org>
Sent: Friday, July 15, 2011 19:14:31
Subject: Re: Snapshot too old from READ-ONLY table (data pump export)


I should have mentioned that the tablespace was placed in read-only mode over 
two weeks ago.  The explanation in the Tom Kyte article that you referenced 
leads me to think that would be enough time to avoid still being affected by the 
delayed block writes.

Sent from my iPad 


On Jul 15, 2011, at 12:59, "Bobak, Mark" <Mark.Bobak_at_proquest.com> wrote:


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 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




application/msword attachment: ora-01555-delayed-block-cleanout.doc