输出如下
table_name = EMR_CASE
column_name = WORD
找到坏块的bad rowid,使用以下plsql脚本
create table bad_rows (row_id ROWID,oracle_error_code number);
set concat off
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 lob_column: WORD
Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE
可以查询bad rowid
select * from bad_rows;
更新空LOB字段来避免ORA-1578,ORA-26040,如果是CLOB类型，将empty_blob()改为empty_clob()
set concat off
update &table_owner.&table_with_lob set &lob_column = empty_blob() where rowid in (select row_id from bad_rows);
将bad rowid lob块移到其他表空间
alter table &table_owner.&table_with_lob move LOB (&&lob_column) store as (tablespace &tablespace_name);
最后别忘记rebuild index