Re: Snapshot too old from READ-ONLY table (data pump export)
From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Mon, 18 Jul 2011 23:33:35 +0200
Message-ID: <CAMHX9JKnc+enh5vmw-zQozif3m2cw-NjCYtRG1tCXBwymAJc9g_at_mail.gmail.com>
Very nice explanation!
Date: Mon, 18 Jul 2011 23:33:35 +0200
Message-ID: <CAMHX9JKnc+enh5vmw-zQozif3m2cw-NjCYtRG1tCXBwymAJc9g_at_mail.gmail.com>
Very nice explanation!
-- Tanel Poder http://blog.tanelpoder.com On Mon, Jul 18, 2011 at 10:56 AM, Chris Dunscombe <cdunscombe_at_yahoo.com>wrote:Received on Mon Jul 18 2011 - 16:33:35 CDT
> 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>
> 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