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>


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

Original text of this message