壞塊的處理方法:
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,<object_id>,<relative_fno>,<block_id>,0) from DUAL;
HI_RID AFTER the corrupt block:
dbms_rowid.rowid_create(1,<object_id>,<relative_fno>,<block_id>+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;
AAANFlAAEAAAAJ1AAA
到這步我們也可以知道一個塊中存放了多少條記錄:
scott@ORCL>select count(*) from t where rowid>='AAANFlAAEAAAAJ0AAA' and rowid<'AAANFlAAEAAAAJ1AAA';
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<high_rowid;
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.