在上篇(table中各种坏块对select/dml操作影响)中说到如果segment header异常了，不能通过ctas来获得相关数据，在群的讨论中，EZIO说到可以通过rowid方式来获得相关数据，通过测试证明，确实可以通过该方法获得数据，以后遇到此类错误，大家也不必惊慌.
创建测试表
SQL> create table t_xifenfei
2 as
3 select * from dba_objects where object_id is not null;
SQL> alter table t_xifenfei
2 add constraint PK_t_xifenfei primary key (object_id)
3 ;
Table altered.
SQL> alter system checkpoint;
System altered.
SQL> select count(*) from chf.t_xifenfei;
COUNT(*)
----------
74762
SQL> select header_file,header_block from
2 DBA_SEGments where segment_name='T_XIFENFEI' AND OWNER='CHF';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 170
dump block
alter system dump datafile 4 block 170; Dump of buffer cache at level 4 for tsn=4 rdba=16777386 Block dump from disk: buffer tsn: 4 rdba: 0x010000aa (4/170) scn: 0x0b8c.3c0092e4 seq: 0x01 flg: 0x04 tail: 0x91e42301 frmt: 0x02 chkval: 0xa531 type: 0x23=PAGETABLE SEGMENT HEADER
通过header_block和dump block确定block 170即为PAGETABLE SEGMENT HEADER
bbed制造SEGMENT HEADER坏块
BBED> set block 170
BLOCK# 170
BBED> set offset 8188
OFFSET 8188
BBED> d
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 170 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0123e492
<32 bytes per line>
BBED> m /x 0123e491
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/oracle/oradata/ora11g/users01.dbf (0)
Block: 170 Offsets: 8188 to 8191 Dba:0x00000000
------------------------------------------------------------------------
0123e491
<32 bytes per line>
BBED> sum apply
Check value for File 0, Block 170:
current = 0xa531, required = 0xa531
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 170
Block 170 is corrupt
Corrupt block relative dba: 0x010000aa (file 0, block 170)
Fractured block found during verification
Data in bad block:
type: 35 format: 2 rdba: 0x010000aa
last change scn: 0x0b8c.3c0092e4 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x91e42301
check value in block header: 0xa531
computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 1
Total Blocks Influx : 2
Message 531 not found; product=RDBMS; facility=BBED
测试segment header坏块后select操作
SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SQL> select /*+index(t PK_T_XIFENFEI)*/ count(rowid) from chf.t_xifenfei t;
COUNT(ROWID)
------------
74762
基于rowid获取segment header 坏块对象数据
SQL> create table chf.bad_rows (table_name varchar2(60),
2 row_id rowid, oracle_error_code number);
Table created.
SQL> DECLARE
2 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3 CURSOR c1 IS select /*+index(t PK_T_XIFENFEI)*/ rowid from chf.t_xifenfei t;
4 r RowIDTab;
5 rows NATURAL := 20000;
6 bad_rows number := 0 ;
7 errors number;
8 error_code number;
9 myrowid rowid;
10 BEGIN
11 OPEN c1;
12 LOOP
13 FETCH c1 BULK COLLECT INTO r LIMIT rows;
14 EXIT WHEN r.count=0;
15 BEGIN
16 FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
17 insert into chf.t_xifenfei_new
18 select /*+ ROWID(A) */ *
19 from chf.t_xifenfei A where rowid = r(i);
20 EXCEPTION
21 when OTHERS then
22 BEGIN
23 errors := SQL%BULK_EXCEPTIONS.COUNT;
24 FOR err1 IN 1..errors LOOP
25 error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
26 myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
27 bad_rows := bad_rows + 1;
28 insert into chf.bad_rows values('chf.t_xifenfei',myrowid, error_code);
29 END LOOP;
30 END;
31 END;
32 commit;
33 END LOOP;
34 commit;
35 CLOSE c1;
36 dbms_output.put_line('Total Bad Rows: '||bad_rows);
37 END;
38 /
Total Bad Rows: 0
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei_new;
COUNT(*)
----------
74762
通过上面pl/sql，基于rowid成功获得segment header 异常对象中的所有数据记录.如果没有主键的表出现该问题,可以参考:使用plsql抢救数据
