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

  • From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
  • To: cdunscombe@xxxxxxxxx
  • Date: Mon, 18 Jul 2011 23:33:35 +0200

Very nice explanation!

--
Tanel Poder
http://blog.tanelpoder.com



On Mon, Jul 18, 2011 at 10:56 AM, Chris Dunscombe <cdunscombe@xxxxxxxxx>wrote:

> 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@xxxxxxxxx>
> *To:* "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
> *Cc:* Oracle-L List <oracle-l@xxxxxxxxxxxxx>
> *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@xxxxxxxxxxxx> 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>
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:895410916429
>
>
>
> Hope it helps,
>
>
>
> -Mark
>
>
>
>
>
> *From:* oracle-l-bounce@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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@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
>
>
>
>
>
>

Other related posts: