Wednesday, May 26, 2010

LOB Corruption prevented export of a Table

Few days back, I noticed the following error while DataPump was exporting a table containing BLOB column:
ORA-31693: Table data object "<<tablename>>" 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 with name "" too small
ORA-22924: snapshot too old

I tried to export that table with the old export utility, but that too was not successful.

Searched the Oracle Support Documents and got the below script:

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;
/

The execution of the above script showed two records have corrupted blocks. I deleted those records and attempted export (using DataPump). This time it was successfull.

I am looking for ways to recover such corrupted blocks.

No comments:

Post a Comment