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

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "brad_peek@xxxxxxxxx" <brad_peek@xxxxxxxxx>, Oracle-L List <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 15 Jul 2011 13:59:18 -0400

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