SCOTT@test01p> create table t1 as select * from all_objects; Table created.
SCOTT@test01p> create table t2 as select * from t1; Table created.
SCOTT@test01p> select rowid,t1.* from t1 where rownum=1; ROWID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O ------------------ ----- ----------- -------------- ---------- -------------- ----------- ------------------- ------------------- ------------------- ------ - - - ---------- ------------ ------- - - AAAaSzAAJAAAAC7AAA SYS I_OBJ1 36 36 INDEX 2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID N N N ########## NONE Y
SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T1'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 107699 107699
SCOTT@test01p> select object_id,data_object_id from dba_objects where owner=user and object_name='T1'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 107699 107701
--//可以发现truncate后data_object_id=107701发生变化.
SCOTT@book> alter system checkpoint; System altered.
--//执行以上3条update命令以sys用户执行.还原回来原来的数据段号. SYS@test01p> alter system flush shared_pool; System altered.
SYS@test01p> alter system flush shared_pool; System altered.
SYS@test01p> UPDATE tab$ set dataobj#=107699 where obj#=107699; 1 row updated.
SYS@test01p> UPDATE seg$ set hwmincr=107699 where hwmincr=107701; 1 row updated.
SYS@test01p> update obj$ set dataobj#=107699 where obj#=107699; 1 row updated.
SYS@test01p> commit ; Commit complete.
SYS@test01p> alter system flush shared_pool; System altered.
4.检查是否能正常显示: SCOTT@book> alter system flush buffer_cache; System altered.
SCOTT@test01p> select rowid,t1.* from t1 where rowid='AAAaSzAAJAAAAC7AAA'; ROWID OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O ------------------ -------------------- -------------------- -------------------- ---------- -------------- -------------------- ------------------- ------------------- ------------------- -------------------- - - - ---------- -------------------- ------------- - - AAAaSzAAJAAAAC7AAA SYS I_OBJ1 36 36 INDEX 2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID N N N ########## NONE Y
--//可以发现使用rowid可以查询对应记录.如果写成如下: SCOTT@test01p> select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB'; select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB' * ERROR at line 1: ORA-08103: object no longer exists
D:\tools\bbed>oerr ora 8103 08103, 00000, "object no longer exists" // *Cause: The object has been deleted by another user since the operation // began, or a prior incomplete recovery restored the database to // a point in time during the deletion of the object. // *Action: Delete the object if this is the result of an incomplete // recovery.
--//我前面提到执行计划TABLE ACCESS BY ROWID RANGE,注意范围这个关键字.这样要访问段头,因为是truncate表后 --//降低了高水位标识,并且我没有修改数据段号,即使我修改数据段号=107699. --//oracle认为要访问的数据块在高水位之上,一样没有结果集. --//而前面的执行计划是TABLE ACCESS BY USER ROWID.直接通过rowid访问块.越过了数据段头的访问.
5.有了以上思路,应该可以恢复全部记录. --//实际上就是当段头损坏.
SCOTT@test01p> CREATE TABLE t1new tablespace lfree AS SELECT * FROM t1 where 1=0; Table created.
set serveroutput on set concat off DECLARE nrows number; rid rowid; dobj number; ROWSPERBLOCK number; BEGIN ROWSPERBLOCK:=736; --估算最大的一个块中记录条数,8K最多736条记录1块. nrows:=0;
select data_object_id into dobj from dba_objects where owner = 'SCOTT' and object_name = 'T1' -- and subobject_name = '
' Add this condition if table is partitioned ;
for i in (select relative_fno, block_id, block_id+blocks-1 totblocks from dba_extents where owner = 'SCOTT' and segment_name = 'T1' -- and partition_name = '
' Add this condition if table is partitioned -- and file_id != This condition is only used if a datafile needs to be skipped due to ORA-376 (A) order by extent_id) loop for br in i.block_id..i.totblocks loop for j in 1..ROWSPERBLOCK loop begin rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1); insert into t1new select /*+ ROWID(A) */ * from t1 A where rowid = rid; if sql%rowcount = 1 then nrows:=nrows+1; end if; if (mod(nrows,10000)=0) then commit; end if; exception when others then null; end; end loop; end loop; end loop; COMMIT; dbms_output.put_line('Total rows:'||to_char(nrows)); END; /
--//再执行前必须修改dba=9,184 到 9,186块中的段号=107699,不然 --//select * from dba_extents where owner=user and segment_name='T1';没有显示.无法恢复.
BBED> sum apply dba 9,187 Check value for File 9, Block 187: current = 0x6ac0, required = 0x6ac0
--//OK现在修改完成.select * from dba_extents where owner=user and segment_name='T1';也能正常显示. --//执行修复脚本,等,脚本很慢,主要块有点多,而且不管什么块都做这样的操作, --//另外每块假设的记录值736有点高,估计200比较合适.对于当前的表.
SCOTT@test01p> alter system flush shared_pool; System altered.
SCOTT@test01p> select rowid,t1.* from t1 where rowid between 'AAAaSzAAJAAAAC7AAA' and 'AAAaSzAAJAAAAC7AAB'; no rows selected --//rowid range 查询一样没有结果,验证了我前面的判断.
SCOTT@test01p> @ aa.txt Total rows:89859 PL/SQL procedure successfully completed. --//我这里大约5分钟,有点慢...^_^.
SCOTT@test01p> select * from t1new minus select * from t2; no rows selected
SCOTT@test01p> select * from t2 minus select * from t1new; no rows selected
SCOTT@test01p> select count(*) from t1new; COUNT(*) ---------- 89859
