Lob Corruption
From: zigzagdna <zigzagdna_at_yahoo.com>
Date: Wed, 22 Feb 2012 18:26:13 -0800 (PST)
Message-ID: <d5aa7b82-3ca7-4b3a-aded-7df25833fd55_at_t15g2000yqi.googlegroups.com>
end if;
end loop;
end; Received on Wed Feb 22 2012 - 20:26:13 CST
Date: Wed, 22 Feb 2012 18:26:13 -0800 (PST)
Message-ID: <d5aa7b82-3ca7-4b3a-aded-7df25833fd55_at_t15g2000yqi.googlegroups.com>
I am running following PL/SQL code (see eblow) from Oracle to
identify LOB corruption because I am getting
ORA-1555 during exports on table containing BLOB, CLOB. I want to
eliminate whether this is because of
LOB corruption. When I run the script, I get following “errors”; but
there is no way I can make those
“errors” go away; because table does not have rows with those rowid’s.
Appreciate your expert insight,
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
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; Received on Wed Feb 22 2012 - 20:26:13 CST