Why is it not possible to rebuild corrupt LOB indexes? So if my LOB index is corrupted, how to recover it?
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.
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); declare page number; len number; c varchar2(10); charpp number := 8132/2; begin 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 begin select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp)) into c from <your_table_with_clcob_column> 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; /
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>;
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):