SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf'
2 size 10m autoextend on next 10m maxsize 1g;
表空间已创建。
SQL> create table t_xifenfei tablespace t_xff
2 as
3 select * from dba_objects;
表已创建。
SQL> select count(*) from t_xifenfei;
73286
二.发现坏块
使用ULtraEdit破坏数据(关闭数据库执行)
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
The "HI_RID" is the first rowid AFTER the corrupt block:
SELECT dbms_rowid.rowid_create(1,,,+1,0) HI_RID
from DUAL;
SQL> col tablespace_name for a30
SQL> col segment_type for a5
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
2 FROM dba_extents
3 WHERE file_id =13
4 AND 373 between block_id AND block_id + blocks - 1 ;
TABLESPACE_NAME SEGME OWNER SEGMENT_NAME
T_XFF TABLE CHF T_XIFENFEI
SQL> SELECT data_object_id
2 FROM dba_objects
3 WHERE object_name = 'T_XIFENFEI' and owner='CHF';
77759
--坏块的最小rowid
SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual;
AAAS+/AANAAAAF1AAA
坏块的最大rowid(block+1得到)
SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual;
AAAS+/AANAAAAF2AAA
四.根据rowid找回数据
SQL> SELECT /+ ROWID(A) / COUNT(*) FROM T_XIFENFEI A
2 WHERE ROWID>='AAAS+/AANAAAAF2AAA';
55858
SQL> SELECT /+ ROWID(A) / COUNT(*) FROM T_XIFENFEI A
2 WHERE ROWID<'AAAS+/AANAAAAF1AAA';
17358
SQL> SELECT 77759-55858-17358 from dual;
4543
SQL> CREATE TABLE T_XIFENFEI_BAK TABLESPACE T_XFF
2 AS
3 SELECT /+ ROWID(A) / * FROM T_XIFENFEI A
4 WHERE ROWID>='AAAS+/AANAAAAF2AAA';
表已创建。
SQL> INSERT INTO T_XIFENFEI_BAK
2 SELECT /+ ROWID(A) / * FROM T_XIFENFEI A
3 WHERE ROWID<'AAAS+/AANAAAAF1AAA';
已创建17358行。
SQL> COMMIT;
提交完成。
SQL> SELECT COUNT(*) FROM T_XIFENFEI_BAK;
73216
五.和dbms_repair解决坏块对比
SQL> CONN / AS SYSDBA
已连接。
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL 过程已成功完成。
SQL> select skip_corrupt from dba_tables where table_name='T_XIFENFEI';
ENABLED
SQL> select count(*) from chf.t_xifenfei;
73216
通过跳过坏块和rowid功能对比可以看出,两者丢失的数据是相同的,如果有index,同样利用rowid结合index,可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞6
添加新评论0 条评论