Subject:         ORA-01555 And Other Errors while Exporting Table With LOBs

Doc ID:         Note:452341.1         Type:         PROBLEM

Last Revision Date:         27-JUL-2007         Status:         MODERATED

In this Document

Symptoms

Cause

Solution

References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2

This problem can occur on any platform.

Symptoms

When attempting to export tables that  contain lobs,

the following error occurs:

Using EXPDP:

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

ORA-31693: Table data object "HARVEST"."HARVERSIONDATA" failed to load/unload and is being

skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

And using EXP:

. . exporting table HARVERSIONDATA

EXP-00056: ORACLE error 1555 encountered

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

Cause

The cause of these errors could be LOB corruption, maintaining the LOBs will solve the problem

with Exp/Expdp.

Solution

1-Run the following script against the LOB tables in order to check for corruption:

set serverout on

exec dbms_output.enable(100000);

declare

pag number;

len number;

c varchar2(10);

charpp number:=8132/2;

begin

FOR R in (

select rowid rid, dbms_lob.getlength(

len

from )

LOOP

if r.len is not null then

for page in 0 .. R.len/charpp

loop

BEGIN

select dbms_lob.substr(,1,1+(page*charpp)) into c

from where rowid=R.rid;

exception

when others then

dbms_output.put_line('Error on rowid ' ||R.rid||' page '||page);

dbms_output.put_line(sqlerrm);

END;

end loop;

END IF;

END LOOP;

end;

2-If 2 consecutive runs show the same rows, then it's a problem with those LOB records.

The easiest thing  would be to delete the LOBs at those ROWIDs and rebuild them (when

possible).

References

Note 66431.1 - LOBS - Storage, Redo and Performance Issues

Note 5212539.8 - Bug 5212539 - LOB corruption for NOCACHE LOBs (ORA-1555/ORA-22924)

Errors

EXP-56 placeholder for OCI error

ORA-22924 snapshot too old

ORA-29913 error in executing %s callout

ORA-1555 "snapshot too old (rollback segment too small)"

ORA-31693 Table data object %s failed to load/unload and is being skippe

Keywords

'LOB'

Help us improve our service. Please email us your comments for this document. .