ORA-01578 ORA-01110 壞塊解決方案

alter session SET EVENTS '10231 trace name context off';

segment_name=SYS_LOB0000119493C00006$$
segment_type=LOBSEGMENT

column_name = WORD

set serveroutput on


declare
  n number;
  error_code number;
  bad_rows number := 0;
  ora1578 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora1578, -1578);
begin
   for cursor_lob in (select rowid rid, &&lob_column from &&table_owner.&table_with_lob) loop
   begin
     n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
   exception
    when ora1578 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,1578);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,error_code);
     commit;   
   end;
  end loop;
  dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);
end;
/

Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE 
可以查詢bad rowid
select * from bad_rows;

update &table_owner.&table_with_lob set &lob_column = empty_blob() where rowid in (select row_id from bad_rows);