关于oracle数据块损坏，简单使用解决方案

例如：数据库损坏表：abc_data

 

1.创建表存放lob损坏行的rowid

create table corrupted_lob_data1 (corrupt_rowid rowid, err_num number);

2.执行如下plsql块，找出存在损坏lob的行

declare
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;
begin
  for cursor_lob in (select rowid r,大字段 from abc_data) loop
    begin
      n := dbms_lob.instr (cursor_lob.大字段, hextoraw ('889911')) ;
exception
when error_1578 then
insert into corrupted_lob_data1 values (cursor_lob.r, 1578);
commit;
when error_1555 then
insert into corrupted_lob_data1 values (cursor_lob.r, 1555);
commit;
when error_22922 then
insert into corrupted_lob_data1 values (cursor_lob.r, 22922);
commit;
end;
end loop;
end;

3.查询结果查看哪些行 blob列有损坏

select * from corrupted_lob_data1;

4.将损坏的blob记录删除
删除这个记录，删除前请保存数据，可以右击保存为excel格式
select * from 表 where rowid in (select corrupt_rowid from corrupted_lob_data1);

delete from 表 where rowid in (select corrupt_rowid from corrupted_lob_data1);

5.备份1
create table abc_aaa_bak as select * from abc_aaa;

5.备份2

expdp 用户/密码 directory=目录名 dumpfile=文件件 logfile=日志名 tables=表名

6.删除旧表

drop table 表名;

7.导入新表1

impdp 用户/密码 directory=目录名 dumpfile=文件件 logfile=日志名 tables=表名

7.导入新表2
insert into abc_aaa select * from abc_aaa_bak;