Dreaded ORA 1555 and EXP-00056 and LOB Corruption
I am on Oracle 10.2.0.4 on HP UNIX 11.2.
I have started getting
EXP-00056: ORACLE error 1555 encountered
ORA-01555: snapshot too old: rollback segment number with name "" too small
ORA-22924: snapshot too old
I have looked into various causes and still no clue why it happening:
1. Undo_retention, it Is set to 5 hours (converted to seconds0> My export backup lasts
For 1.5 to 2 hours.
2. My undo tablespace size is 28GB. Looking at undo advisor I only need 5GB.
3. Yes, my table where error message comes consistent has LOB (BLOB) column.
I did check for LOB corruption as per metalink note (script shown below) and it gives
Me messages:
rowid AABV8QAAJAAGAn6AAM is corrupt. ORA-01403: no data found
rowid AABV8QAAKAAAcaAAAX is corrupt. ORA-01403: no data found
rowid AABV8QAAKAAAcamABr is corrupt. ORA-01403: no data found
rowid AABV8QAAKAAAcamABu is corrupt. ORA-01403: no data found
I do not know what to make of these messages because when I look in my table where problem
Where error occurs:
Select pr_id, col1, col2 from pr where rowed in (above rowids)’; there are
No rows. What does this mean? Why it is corruption.
Below is the script used to find LOB corruption…
declare
pag number;
len number;
c varchar2(10);
charpp number := 8132/2;
begin
for r in (select rowid rid, dbms_lob.getlength (LS_VALUE) len
from PR_ADDTL_DATA) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (LS_VALUE, 1, 1+ (page * charpp))
into c
from PR_ADDTL_DATA
where rowid = r.rid;
exception
when others then
dbms_output.put_line('rowid ' || r.rid || ' is corrupt. ' || sqlerrm);
commit;
end;
end loop;
end if;
end loop;
end;
/