Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?

  • Can you explain what makes you believe your LOB index is corrupt? – Phil Sumner Jul 24 '15 at 11:10
  • validate logical database says so. – tesla747 Jul 24 '15 at 11:27

The alert log should tell you if a data block is corrupt, no need to validate index structure. That will lock your table until completed. If no backups you might try to exporting the data using expdp. Then drop the table and re-create it and import using impdp. You will need to create the indexes, constraints and grants as well. Could also use CTAS.

| improve this answer | |
  • The export will be able to do the job even if the index is corrupted? – tesla747 Jul 26 '15 at 13:07
  • Yes, just make sure you do a data_only export. – Stringer Jul 26 '15 at 13:38
  • And what happens if I leave that corrupted index untouched forever..what impact does it have. – tesla747 Jul 26 '15 at 18:36
  • A) index will not be used when that column is reference in a query predicate B) each time that column is accessed an error will be written to the alert log – Stringer Jul 26 '15 at 22:15

In LOB columns, the data and index are intertwined. The index cannot be operated on like a normal index. Use this script to identify the rows that are corrupted:

set serverout on
exec dbms_output.enable(100000);
  page    number;
  len    number;
  c      varchar2(10);
  charpp number := 8132/2;

  for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len
            from   <your_table_with_clcob_column>) loop
    if r.len is not null then
      for page in 0..r.len/charpp loop
          select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
          into   c
          from   <your_table_with_clcob_column> 
          where  rowid = r.rid;

          when others then
            dbms_output.put_line ('Error on rowid ' ||R.rid||' page '||page);
            dbms_output.put_line (sqlerrm);
      end loop;
    end if;
  end loop;

You'll need to either delete the rows or update the LOB column to an empty LOB with:

update <your_lob_table> 
set <your_lob_column> = empty_blob() 
where rowid=<your_corrupt_rowid>;
| improve this answer | |

What is the output of:

select * from v$database_block_corruption?

Unlike regular indexes, LOB indexes can't be dropped/ recreated, because you would have to drop the column instead. So, assuming your backups are valid, you can attempt to perform block media recovery (BMR):


| improve this answer | |
  • yes yes have checked everything. I do not have backups. – tesla747 Jul 25 '15 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.