参考文档：Troubleshooting Assistant: Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g (文档 ID 1598103.2)
通常坏块有如下报错：
ORA-01578: ORACLE data block corrupted (file # 36, block # 721776)
处理：
1、定位坏块所在对象类型和名称：
col TABLESPACE_NAME for a20
col OWNER for a10
col SEGMENT_NAME for a40
select tablespace_name,segment_type,owner,segment_name From dba_extents Where RELATIVE_FNO=36 and 721776 between block_id and block_id+blocks-1;
TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
-------------------- ------------------------------------ ---------- ----------------------------------------
TS_PFM LOBSEGMENT DRM_PFM SYS_LOB0000092671C00013$$
通过LOB段定位所在表：
select owner,table_name from dba_lobs where owner='DRM_PFM' and segment_name='SYS_LOB0000092671C00013$$';
OWNER TABLE_NAME
------------------------------------------------------------ ------------------------------------------------------------
DRM_PFM ASYNCHRON_REQUEST
由于该坏块在LOB段上，而LOB段我们并不能直接访问，故而不能通过构造ROWID的方式来定位损坏的数据行，只能通过扫描的方式来定位了，有如下匿名块：
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 (JMS_BODY_MSG) len
from DRM_PFM.ASYNCHRON_REQUEST) loop
if r.len is not null then
for page in 0..r.len/charpp loop
begin
select dbms_lob.substr (JMS_BODY_MSG, 1, 1+ (page * charpp))
into c
from DRM_PFM.ASYNCHRON_REQUEST
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;
/
输出：
Error on rowid AAAj2MAAgAAApENAAC page 0
ORA-01578: ORACLE data block corrupted (file # 34, block # 799130)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 1
ORA-01578: ORACLE data block corrupted (file # 34, block # 799130)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 2
ORA-01578: ORACLE data block corrupted (file # 34, block # 799131)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 3
ORA-01578: ORACLE data block corrupted (file # 34, block # 799193)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 4
ORA-01578: ORACLE data block corrupted (file # 34, block # 799253)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 5
ORA-01578: ORACLE data block corrupted (file # 34, block # 799134)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 6
ORA-01578: ORACLE data block corrupted (file # 34, block # 799254)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 7
ORA-01578: ORACLE data block corrupted (file # 34, block # 799317)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 8
ORA-01578: ORACLE data block corrupted (file # 34, block # 799258)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 9
ORA-01578: ORACLE data block corrupted (file # 34, block # 799318)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 10
ORA-01578: ORACLE data block corrupted (file # 34, block # 799320)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 11
ORA-01578: ORACLE data block corrupted (file # 34, block # 799321)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 12
ORA-01578: ORACLE data block corrupted (file # 34, block # 799381)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApENAAC page 13
ORA-01578: ORACLE data block corrupted (file # 34, block # 799322)
ORA-01110: data file 34: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb9cn_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApF1AAY page 0
ORA-01578: ORACLE data block corrupted (file # 36, block # 725489)
ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApF1AAY page 1
ORA-01578: ORACLE data block corrupted (file # 36, block # 725489)
ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApF1AAY page 2
ORA-01578: ORACLE data block corrupted (file # 36, block # 725553)
ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApF1AAY page 3
ORA-01578: ORACLE data block corrupted (file # 36, block # 725617)
ORA-01110: data file 36: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kbb37_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApIoAAe page 0
ORA-01578: ORACLE data block corrupted (file # 32, block # 748627)
ORA-01110: data file 32: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb8m4_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
Error on rowid AAAj2MAAgAAApIoAAe page 1
ORA-01578: ORACLE data block corrupted (file # 32, block # 748627)
ORA-01110: data file 32: '/oradata/drmdb/DRMDBSTD/datafile/o1_mf_ts_pfm_fy4kb8m4_.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-06512: at "SYS.DBMS_LOB", line 1092
ORA-06512: at line 1
PL/SQL procedure successfully completed.
通过UE排序后，可得到全部报错的ROWID
2、定位到损坏的行后，通过将行Lob列置空的形式可以重置该块（数据清空）
update DRM_PFM.ASYNCHRON_REQUEST set JMS_BODY_MSG=empty_clob() where rowid in ('AAAj2MAAgAAApENAAC','AAAj2MAAgAAApF1AAY','AAAj2MAAgAAApIoAAe');
commit;
alter table DRM_PFM.ASYNCHRON_REQUEST move lob(JMS_BODY_MSG);
注意：该操作后会导致表上所有索引失效，需要重建索引；
