TRUNCATE TABLE恢复-脚本

SQL> create table rescureora.rescureora_table as select * from dba_objects;
Table created. 
SQL> select count(*) from rescureora.rescureora_table;
  COUNT(*)
----------
     86877   
SQL> truncate table rescureora.rescureora_table;
Table truncated.

SQL> select obj#,dataobj# from obj$ where name='RESCUREORA_TABLE'; 
      OBJ#   DATAOBJ#
---------- ----------
     87903      87904       
SQL> select SQL_REDO from V$LOGMNR_CONTENTS where table_name='OBJ$' and SQL_REDO LIKE '%87903%'; 
SQL_REDO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
update "SYS"."OBJ$" set "OBJ#" = '87903', "DATAOBJ#" = '87904', "TYPE#" = '2', "CTIME" = TO_DATE('01-DEC-19', 'DD-MON-RR'), "MTIME" = TO_DATE('01-DEC-19', 'DD-MON-RR'), "STIME" = TO_DATE('01-DEC-19',
'DD-MON-RR'), "STATUS" = '1', "FLAGS" = '0', "OID$" = NULL, "SPARE1" = '6', "SPARE2" = '1' where "OBJ#" = '87903' and "DATAOBJ#" = '87903' and "TYPE#" = '2' and "CTIME" = TO_DATE('01-DEC-19', 'DD-MON-
RR') and "MTIME" = TO_DATE('01-DEC-19', 'DD-MON-RR') and "STIME" = TO_DATE('01-DEC-19', 'DD-MON-RR') and "STATUS" = '1' and "FLAGS" = '0' and "OID$" IS NULL and "SPARE1" = '6' and "SPARE2" = '1' and R
OWID = 'AAAAASAABAAAVKtAAE';

由于需要根据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;