expdp and ORA-01555

From: <richa03_at_gmail.com>
Date: Tue, 26 Aug 2008 08:49:40 -0700
Message-ID: <26279fb90808260849g1977858aie863ad6c95ef8a92@mail.gmail.com>


64-bit 10.2.0.3 on Solaris 10 x86

 When trying to export a single table, we get: ORA-01555: snapshot too old: rollback segment number with name "" too small  The table contains out of line LOBs using PCTVERSION of 10, however, these LOBs are never updated, so the PCTVERSION should not be the issue. [This has been proven through the MON_MODS$ table using the OBJECT_ID column of DBA_OBJECTS - see below]
We have scanned this table per Oracle Note 452341.1 and there is no corruption.
We have created a larger undo segment - from 1GB to 5GB. We set event 1555 to dump errorstack at level 3 and have the trace file. We have changed undo_retention from 900 to 21140 per v$undostat. The columns SSOLDERRCNT and NOSPACEERRCNT are 0. Furthermore, the columns UNXPSTEALCNT & EXPSTEALCNT are all 0 and the column MAXQUERYLEN was not over the setting of the undo_retention parameter (21140 - the high was 8069) during the export.

The export fails at about 141009977344 bytes (5 runs, sometimes earlier with less bytes).

The table is about 100M records and about 275GB. It's structure is:

Name                                      Null?    Type
----------------------------------------- -------- --------------
TRHS_PKEY                                 NOT NULL NUMBER
TRHS_SEQ                                  NOT NULL NUMBER
TRAN_KEY                                  NOT NULL NUMBER
BSTP_KEY                                           NUMBER
TRSP_KEY                                           NUMBER
TRHS_DESC                                 NOT NULL VARCHAR2(256)
TRHS_DOC_URL                                       VARCHAR2(512)
TRHS_DOC                                           BLOB
TRHS_DOC_SIZE                             NOT NULL NUMBER
TRHS_ZIPPED                               NOT NULL NUMBER
TRHS_CONTENT_TYPE                                  VARCHAR2(128)
TRHS_CREATED_DATE                         NOT NULL DATE

 The table is insert only - about 700 records per min. select * from SYS.MON_MODS$ where OBJ# = 32716;

      OBJ# INSERTS UPDATES DELETES TIMESTAMP FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ----------


    32716 36837918 0 1320167 26-AUG-08 0 0

 The export was tested in a test environment (without load) and succeeded - 170341232640 bytes.

 Stack trace:
Summary Stack (to Full stack) (to Function List) koklierr NAME: koklisdisk - KOK Lob Internal Set DISK part of locator. kluuldcs
kluprd
kluuscn
kluuld
kpodpuls
opiodr OPIODR: ORACLE code request driver - route the current request

 We have an open SR with Oracle, but, well, you know...

We can try to simulate load on this object, however this will be difficult if not impossible in test.

Under insert, I would expect the internal LOB index to be updated - does this count against undo?

If so, where is this undo stored - normal undo segments or internal to the LOB object (like the pctversion space)?

Does expdp lock this undo to ensure consistency?

TIA

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 26 2008 - 10:49:40 CDT

Original text of this message