坏块的处理方法
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的一个实用处吧
