熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

ORACLErowdi壞塊恢復案例

2013-11-13 22:15:06  來源: Oracle 

  壞塊的處理方法

  ROWID RANGE SCAN

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

  select>sys@orcl>select text from user_source where name=DBMS_ROWID;

  這裡有詳細的說明不用再去翻什麼資料了

  使用DBMS_ROWID 確定壞塊的ROWID RANGE

  LOW_RID INSIDE the corrupt block:

  SELECT dbms_rowidrowid_create(<object_id><relative_fno><block_id>) from DUAL;

  HI_RID AFTER the corrupt block:

  dbms_rowidrowid_create(<object_id><relative_fno><block_id>+) from DUAL;

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

  建一個臨時表

  CREATE TABLE temp_t AS SELECT * FROM t Where =;

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

  OBJECT_ID

  

  

  select>sys@ORCL>select RELATIVE_FNOblock_idblocks from dba_extents where wner=SCOTT and segment_name=T order by block_id;

  RELATIVE_FNO   BLOCK_ID     BLOCKS

  

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                   

                 

                

                

                

                

  select>sys@ORCL>select dbms_rowidrowid_create() from dual;

  DBMS_ROWIDROWID_C

  

  AAANFlAAEAAAAJAAA

  確定一個塊的hirowid

  select>sys@orcl>select dbms_rowidrowid_create() from dual;

  DBMS_ROWIDROWID_C

  

  AAANFlAAEAAAAJAAA

  到這步我們也可以知道一個塊中存放了多少條記錄

  select>scott@ORCL>select count(*) from t where rowid>=AAANFlAAEAAAAJAAA and rowid<AAANFlAAEAAAAJAAA;

  COUNT(*)

  

  

  保存未損壞的數據

  declare

  cursor l_cur

  is

  select block_idblocks 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 im;

  while(l_cur%found) loop

  for t in ii+m loop

  select dbms_rowidrowid_create(t) into  low_rowid from dual;

  select dbms_rowidrowid_create(t+) into high_rowid from dual;

  insert into temp_t select /*+rowid(a)*/ * from t a where rowid>=low_rowid and

  rowid<high_rowid;

  end loop;

  fetch l_cur into im;

  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);

   rows deleted

  然後驗證

  SQL> run

     select ownerobject_namesubobject_nameobject_iddata_object_idobject_typecreatedLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARYcount(*) from temp_t group

     by ownerobject_namesubobject_nameobject_iddata_object_idobject_typecreatedLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARY

    minus

     select ownerobject_namesubobject_nameobject_iddata_object_idobject_typecreatedLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARYcount(*) from t group

  *  by ownerobject_namesubobject_nameobject_iddata_object_idobject_typecreatedLAST_DDL_TIMETIMESTAMPSTATUSTEMPORARYGENERATEDSECONDARY

  no rows selected

  重建tableindexforeign constrain table

  同時注意下rowid是位的結構即

  AAANFlAAEAAAAJAAA

  數據對象編號(前位)文件編號(再位)塊編號(再位)行編號(最後位)

  以上就是損壞塊的基於rowid的修復方式也是rowid的一個實用處吧


From:http://tw.wingwit.com/Article/program/Oracle/201311/18598.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.