由于需要根据dataobj#去匹配，所以需要修改obj$，修改完成之后需要flush shared_pool
注意：如果有lob，truncate不会更改lob index的dataobj#,只需要修改lob的dataobj#即可
SQL> update obj$ set dataobj#=87903 where obj#=87903;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
设置表空间为read only，避免数据被覆盖
SQL> alter tablespace users read only;
Tablespace altered.
具体抽取脚本如下：
注意：恢复的表需要在重新指定一个表空间，避免覆盖数据。本脚本示例将表恢复到了system表空间。
declare
v_fno number;
v_s_bno number;
v_e_bno number;
v_rowid rowid;
nrows number;
v_owner varchar2(100):='RESCUREORA';
v_table varchar2(100):='RESCUREORA_TABLE';
v_o_owner varchar2(100):='SYS';
v_o_table varchar2(100):='RESCUREORA_TABLE';
v_dataobj number;
v_sql varchar2(4000);
v_tablespace varchar2(100);
begin
select data_object_id into v_dataobj from dba_objects where owner=v_owner and object_name=v_table;
select tablespace_name into v_tablespace from dba_tables where owner=v_owner and table_name=v_table;
for i in (select relative_fno,block_id,blocks
from dba_extents
where owner=v_owner and segment_name=v_table and extent_id=0
union all
select relative_fno,block_id,blocks
from dba_free_space
where tablespace_name=v_tablespace
union all
select relative_fno,block_id,blocks from (
select relative_fno,block_id,blocks,row_number()over(partition by owner,segment_name,partition_name order by extent_id desc) rn
from dba_extents
where tablespace_name=v_tablespace and extent_id>0) where rn=1) loop
v_fno:=i.relative_fno;
v_s_bno:=i.block_id;
v_e_bno:=i.block_id+i.blocks-1;
for j in v_s_bno .. v_e_bno loop
begin
for x in 0 .. 999 loop
v_rowid:=dbms_rowid.rowid_create(1,v_dataobj,v_fno,j,x);
v_sql:='insert into '||v_o_owner||'.'||v_o_table||' select * from '||v_owner||'.'||v_table||' where rowid=:1';
execute immediate v_sql using v_rowid;
end loop;
exception
when others then
null;
end;
commit;
end loop;
end loop;
end;
/
SQL> select count(*) from sys.rescureora_table;
COUNT(*)
----------
86877
SQL> update obj$ set dataobj#=87904 where obj#=87903;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
