Re: Snapshot too old from READ-ONLY table (data pump export)
- From: David Roberts <big.dave.roberts@xxxxxxxxxxxxxx>
- To: brad_peek@xxxxxxxxx
- Date: Mon, 18 Jul 2011 14:29:47 +0100
The problem with delayed block cleanout only occurs after the tablespace was
placed in read only mode and only when the undo has been aged out.
The fact that the tablespace is read only is the thing that stops Oracle
from performing the clearout.
The amount of elapsed time since the tablespace was made read only isn't a
factor.
Dave
On Fri, Jul 15, 2011 at 7:14 PM, Brad Peek <brad_peek@xxxxxxxxx> wrote:
> 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
> ****
>
> ** **
>
> 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:
