-- 用于恢复truncate table
-- 作者：Oracle恢复实录公众号作者
-- 2020-01-13
-- 详细请参考文章《TRUNCATE TABLE恢复系列二：PL/SQL恢复》https://www.modb.pro/db/14957
declare
   v_fno number;
   v_s_bno number;
   v_e_bno number;
   v_rowid rowid;
   nrows number;
begin
   for i in (select relative_fno,block_id,blocks
               from dba_extents
                          where owner='RESCUREORA' and segment_name='RESCUREORA_TABLE' and extent_id=0  -- RESCUREORA和RESCUREORA_TABLE 均为原表
             union all
             select relative_fno,block_id,blocks
               from dba_free_space
              where tablespace_name in
                          (select tablespace_name
                             from dba_tables
                                where owner='RESCUREORA' and table_name='RESCUREORA_TABLE')  -- RESCUREORA和RESCUREORA_TABLE 均为原表
                         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 in
                          (select tablespace_name
                             from dba_tables
                                where owner='RESCUREORA' and table_name='RESCUREORA_TABLE') and extent_id>0)  -- RESCUREORA和RESCUREORA_TABLE 均为原表
                           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, 87903,v_fno,j,x); -- 87903 为dataobj#
           insert into sys.rescureora_table select * from rescureora.rescureora_table where rowid=v_rowid;
            -- 其中：sys.rescureora_table 为目标表，rescureora.rescureora_table为原表
         end loop;
        exception
          when others then
            null;
        end;
        commit;
       end loop;
    end loop;
end;
/