SQL> select count(*) from ll_t1;        // 此实验表的行数


  COUNT(*)
----------
    207760


SQL>  select EXTENT_ID,FILE_ID,BLOCK_ID,BYTES,BLOCKS from dba_extents  where  segment_name='LL_T1';     //查询此表所在file#等信息


 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS
---------- ---------- ---------- ---------- ----------
         0          4      10865      65536          8
         1          4      10873      65536          8
         2          4      10881      65536          8
         3          4      10889      65536          8
         4          4      10897      65536          8
         5          4      10905      65536          8
         6          4      10913      65536          8
         7          4      10921      65536          8
         8          4      10929      65536          8
         9          4      10937      65536          8
        10          4      10945      65536          8
        。。。。。。


94 rows selected.


SQL>  select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from ll_t1 where rownum=1;     //第一行数据的FILE#和BLOCK#，这里以这个块作为实验块。


     FILE#     BLOCK#
---------- ----------
         4      10868


SQL>  select file_id,file_name from dba_data_files where file_id=4;     //FILE#为4的数据文件


   FILE_ID     FILE_NAME
---------        --------------------------------------------------------------------------------

    4            /home/oracle/oradata/ora10g/users01.dbf

SQL>  select count(*) from (select dbms_rowid.rowid_block_number(rowid) block# from ll_t1) where block#=10868;    //BLOCK#为10868的实验块共有91行数据。


  COUNT(*)
----------
        91


BBED

如果没有安装，或者不能使用，参考 http://blog.itpub.net/29289867/viewspace-1978370/ 

oracle@:/home/oracle> bbed
Password:                     --默认是blockedit


BBED: Release 2.0.0.0.0 - Limited Production on Tue Jan 19 09:17:10 2016


Copyright (c) 1982, 2007, Oracle.  All rights reserved.


************* !!! For Oracle Internal Use only !!! ***************


BBED> set blocksize 8192
        BLOCKSIZE       8192


BBED> set listfile '/home/oracle/filelist.txt'
        LISTFILE        /home/oracle/filelist.txt


BBED> set width 200
        WIDTH           200


BBED>  set mode edit
        MODE            Edit
BBED> set dba 3,10868                                                     //文件号为listfile对应的文件号，block对应查询出的号
        DBA             0x00c02a74 (12593780 3,10868)

BBED> verify 
DBVERIFY - Verification starting
FILE = /home/oracle/oradata/ora10g/users01.dbf
BLOCK = 10868

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

BBED>  corrupt
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block marked media corrupt.                                                 //说明此块已经标记为坏块
          

BBED> quit

如果数据库中不能生效，可以重启数据库！

  SQL> startup force
    ORACLE instance started.
    
    Total System Global Area  626327552 bytes
    Fixed Size                  2215944 bytes
    Variable Size             289411064 bytes
    Database Buffers          331350016 bytes
    Redo Buffers                3350528 bytes
    Database mounted.
    Database opened.
  SQL> conn scott/tiger
    Connected.

这时在sqlplus中查询实验表，会报错，有坏块！


SQL>  select count(*) from LL_T1;
 select count(*) from LL_T1
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 10868)
ORA-01110: data file 4: '/home/oracle/oradata/ora10g/users01.dbf'


SQL> 

oracle@:/home/oracle/oradata/ora10g>  dbv file=users01.dbf blocksize=8192    //DBV检查数据文件

DBVERIFY: Release 10.2.0.4.0 - Production on Mon Jan 18 16:49:14 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = users01.dbf
Page 10868 is marked corrupt
Corrupt block relative dba: 0x01002a74 (file 4, block 10868)
Bad header found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00c02a74
 last change scn: 0x0000.00000000 seq: 0xff flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x000006ff
 check value in block header: 0x5f3a
 computed block checksum: 0x0

DBVERIFY - Verification complete

Total Pages Examined         : 14720
Total Pages Processed (Data) : 4917
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2252
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 3042
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 4508
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 7527331 (0.7527331)

在这种情况下，如果有备份，可以从备份中恢复，如果没有，那么坏块中的数据肯定要丢失了。数据不能执行导出操作！

 [oracle@ETBDE oradata]$ exp scott/tiger file=/home/oracle/ll_t1.dump tables=ll_t1

  Export: Release 10.2.0.4.0 - Production on Mon May 18 21:44:43 2015
  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  Export done in US7ASCII character set and AL16UTF16 NCHAR character set
  server uses AL32UTF8 character set (possible charset conversion)
  About to export specified tables via Conventional Path ...
  . . exporting table                          LL_T1
  EXP-00056: ORACLE error 1578 encountered
  ORA-01578: ORACLE data block corrupted (file # 4, block # 10868)
  ORA-01110: data file 4: '/home/oracle/oradata/ora10g/users01.dbf'
  Export terminated successfully with warnings.

如果我们使用内部事件10231 EVENT来跳过坏块。Event 10231 允许Oracle 在全表扫描时可以忽略某些损坏的数据块来检索数据，用户可以执行 export 或 "create table as select" 操作来找回在数据文件中那些没有损坏的数据。

注意：在损坏块上的数据将被丢失。


SQL> ALTER SYSTEM SET EVENTS '10231 trace name context forever,level 10' ;


System altered.

SQL> select count(*) from scott.LL_T1; 

  COUNT(*)
----------
    207669
    这个时候使用exp导出也是没有问题的：
 [oracle@ETBDE oradata]$ exp scott/tiger file=/home/oracle/ll_t1.dump tables=ll_t1

  Export: Release 10.2.0.4.0 - Production on Mon May 18 21:47:40 2015
  
  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
  
  Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  Export done in US7ASCII character set and AL16UTF16 NCHAR character set
  server uses AL32UTF8 character set (possible charset conversion)
  
  About to export specified tables via Conventional Path ...
  . . exporting table                          LL_T1     292564 rows exported
  Export terminated successfully without warnings.

查找具体坏块影响的具体的object：

Select 'Block 10868 of file '||f.file_name||' :
   '||decode(e.owner, NULL, e.segment_name,
                      e.owner||'.'||e.segment_name)||
         decode(e.partition_name, NULL, '', '.'||e.partition_name)||
         ' ('||e.segment_type||')' obj
from dba_extents e, dba_data_files f
where f.file_id = 4
  and e.file_id = 4
  and 12 between e.block_id and e.block_id+e.blocks-1;

知道损坏的BLOCK是属于那个OBJECT之后,可以用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS 来处理：

DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
flags IN BINARY_INTEGER DEFAULT SKIP_FLAG);


SQL> BEGIN
         DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCOTT','LL_T1');   //用户名和表名
         END;
        /
 
PL/SQL procedure successfully completed.


SQL> select count(*) from scott.LL_T1;                 //有人说现在还是不能查询全表，我这里实验的可以。也可以EXPORT或者CREATE TABLE AS方法处理。


  COUNT(*)
----------
    207669


SQL> select 207760-207669 from dual;                 //新的的表行数缺少了正好一个块的行数


207760-207669
-------------
           91            



SQL> alter system set events '10231 trace name context off';   //关闭10231事件


System altered.


SQL> begin
         DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('SCOTT','LL_T1',flags=>dbms_repair.noskip_flag);     //通过使用noskip_flag 恢复正常扫描,我这里没有使用，也正常查询了，不知道具体作用。
         end;       
        /

PL/SQL procedure successfully completed.