基于rowid的坏块修复

　坏块的处理方法：

  ROWID RANGE SCAN

  首先我们要知道dbms_rowid的使用方法：

  sys@orcl>select text from user_source where name='DBMS_ROWID';
    这里有详细的说明，不用再去翻什么资料了。

　　使用DBMS_ROWID 确定坏块的ROWID RANGE
　　
             LOW_RID INSIDE the corrupt block:
　　SELECT dbms_rowid.rowid_create(1,,,,0) from DUAL;    
　　HI_RID AFTER the corrupt block:
　　 dbms_rowid.rowid_create(1,,,+1,0) from DUAL;
　　
　　　　            

        scott@ORCL>create table t as select * from all_objects;

          建一个临时表
　　CREATE TABLE temp_t AS SELECT * FROM t Where 1=0;

      sys@ORCL>select object_id from all_objects where wner='SCOTT' and object_name='T';

          OBJECT_ID
         ----------
          53605

      sys@ORCL>select RELATIVE_FNO,block_id,blocks from dba_extents where wner='SCOTT' and segment_name='T' order by block_id;

RELATIVE_FNO   BLOCK_ID     BLOCKS
------------ ---------- ----------
           4        625          8
           4        649          8
           4        657          8
           4        697          8
           4        705          8
           4        713          8
           4        721          8
           4        801          8
           4        809          8
           4        817          8
           4        825          8
           4        833          8
           4        841          8
           4        849          8
           4        857          8
           4        865          8
           4        905        128
           4       1033        128
           4       1161        128
           4       1289        128
           4       1417        128      


         
   sys@ORCL>select dbms_rowid.rowid_create(1,53605,4,628,0) from dual;         
 
DBMS_ROWID.ROWID_C
------------------
AAANFlAAEAAAAJ0AAA  

              确定一个块的hirowid：
 
          
   sys@orcl>select dbms_rowid.rowid_create(1,53605,4,629,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAANFlAAEAAAAJ1AAA   

  到这步我们也可以知道一个块中存放了多少条记录：

  scott@ORCL>select count(*) from t where rowid>='AAANFlAAEAAAAJ0AAA' and rowid
  COUNT(*)
----------
        91

保存未损坏的数据
　　declare
  cursor l_cur
    is
  select block_id,blocks from dba_extents
        where wner='SCOTT' and segment_name='T';
  i pls_integer;
  m pls_integer;
  t pls_integer;
  low_rowid  rowid;
  high_rowid rowid;
begin
  open l_cur;
  fetch l_cur into i,m;
  while(l_cur%found) loop
   for t in i..i+m loop
      select dbms_rowid.rowid_create(1,53605,4,t,0) into  low_rowid from dual;
      select dbms_rowid.rowid_create(1,53605,4,t+1,0) into high_rowid from dual;
      insert into temp_t select /*+rowid(a)*/ * from t a where rowid>=low_rowid and
             rowid    end loop;
   fetch l_cur into i,m;
  end loop;
  close l_cur;
end;

如果出现重复行，可以这样删除 之：

SQL> delete from temp_t where rowid not in (select min(rowid) from temp_t group by object_id);

405 rows deleted.

然后验证：

SQL> run
  1   select owner,object_name,subobject_name,object_id,data_object_id,object_type,created,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,count(*) from temp_t group
  2   by owner,object_name,subobject_name,object_id,data_object_id,object_type,created,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY
  3  minus
  4   select owner,object_name,subobject_name,object_id,data_object_id,object_type,created,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,count(*) from t group
  5*  by owner,object_name,subobject_name,object_id,data_object_id,object_type,created,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY

no rows selected

重建table,index,foreign constrain table.

  同时注意下rowid是6363共18位的结构即：
        AAANFlAAEAAAAJ1AAA                                   
数据对象编号（前6位）文件编号（再3位）块编号（再6位）行编号（最后3位）；

 以上就是损坏块的基于rowid的修复方式，也是rowid的一个实用处吧。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23102627/viewspace-631291/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23102627/viewspace-631291/