使用ORWID模拟坏块并修复过程 - 数据库编程

test，制定到表空间，插入数据
2) 各种方式修改文件，创建坏块，比如编辑数据文件，删除修改其中一个字符，ultraedit工具可行

2、查询坏块
比如查询坏块的表白文件号10，快好100----ORA-01578: ORACLE 数据块损坏
3、查询其它信息
The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,,,,0) LOW_RID
from DUAL;

The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,,,+1,0) HI_RID
from DUAL;
1) 根据段名找到数据对象号
查找段名
SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id =13
AND 100 between block_id AND block_id + blocks - 1 ;
查找数据对象号
SELECT data_object_id
FROM dba_objects
WHERE object_name = 'XXX---上一部找的set_name' and owner='上一部查找的owner';
结果比如是12345
查找最小快rowid和最大rowid
select dbms_rowid.rowid_create(1, 12345,10,100,0) from dual;
--结果是AAAS+/AANAAAAF1AAA
select dbms_rowid.rowid_create(1, 12345,10,101,0) from dual;
---结果是AAAS+/AANAAAAF2AAA

4、找回
1、找到坏块数据量&保存插入临时表
SELECT /*+ ROWID(A) */ COUNT(*) FROM test
WHERE ROWID>='AAAS+/AANAAAAF2AAA';

CREATE TABLE test_TMP TABLESPACE XXX
AS
SELECT /*+ ROWID(A) */ * FROM test
WHERE ROWID>='AAAS+/AANAAAAF2AAA';
SELECT /*+ ROWID(A) */ COUNT(*) FROM test
WHERE ROWID<'AAAS+/AANAAAAF2AAA';

CREATE TABLE test_TMP TABLESPACE XXX
AS
SELECT /*+ ROWID(A) */ * FROM test
WHERE ROWID<'AAAS+/AANAAAAF2AAA';

5，对比
原表对比
exec dbms_repair.skip_corrupt_blocks('CENTER','TEST');
查询是否成功
select skip_corrupt from dba_tables where table_name='TEST';
查询count数量，然后跟上边的对比

使用ORWID模拟坏块并修复过程(二)