壞塊的處理方法
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
