以下是处理问题时的参考文章以及部分代码引用
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | 为了能够分批次执行，所以将所有大字段的表名和列名写入到一个表中，并打标识create table temp_clob(table_name varchar2(32),column_name varchar2(40),sfcl varchar2(2) )insert into temp_clob select table_name,column_name,'否' from cols where data_type='CLOB' order by table_name,column_name--建立记录表，记录找到的损毁字段所在的表，列，行create table corrupt_lobs (corrupt_rowid rowid,table_name varchar2(32),column_name varchar2(32));遍历所有字段的值，找出损毁的clob字段-- Created on 2018-07-04 by ADMINISTRATORdeclare v_cur_CKD sys_refcursor; sqltext varchar2(200); error_1578 exception; error_1555 exception; error_22922 exception; pragma exception_init(error_1578,-1578); pragma exception_init(error_1555,-1555); pragma exception_init(error_22922,-22922); n number; row_id rowid; clobtext clob; begin -- Test statements here for cursor_lob in (select table_name,column_name from temp_clob where sfcl='否' and rownum<100 order by table_name,column_name) loop -- dbms_output.put_line(cursor_lob.table_name||','||cursor_lob.column_name); sqltext :='select rowid r, '||cursor_lob.column_name||' from '||cursor_lob.table_name; open v_cur_CKD for sqltext; fetch v_cur_CKD into row_id,clobtext; while v_cur_CKD%found loop begin n:=dbms_lob.instr(clobtext,hextoraw('889911')); exception when error_1578 then insert into corrupt_lobs values (row_id,cursor_lob.column_name,cursor_lob.table_name); commit; when error_1555 then insert into corrupt_lobs values (row_id,cursor_lob.column_name,cursor_lob.table_name); commit; when error_22922 then insert into corrupt_lobs values (row_id,cursor_lob.column_name,cursor_lob.table_name); commit; end; fetch v_cur_CKD into row_id,clobtext; -- dbms_output.put_line(row_id); end loop; close v_cur_CKD; update temp_clob set sfcl='是' where table_name=cursor_lob.table_name and column_name=cursor_lob.column_name; end loop; end; |
恢复备份数据至测试库，找到生产环境中损毁的记录进行恢复，如果恢复不了就更新成null,防止业务报错
随时随地看视频
