oracle truncate 后数据找回

mahan

declare
  v_table_owner varchar2(32) default upper('&old_owner');
  v_table_name varchar2(32) default upper('&old_table_name');
  v_new_owner varchar2(32) default upper('&new_owner');
  v_new_table_name varchar2(32) default upper('&new_table_name');
   v_data_object_id number;
   v_tablespace_name varchar2(32);
   v_file_no number;
   v_start_block_no number;
   v_end_block_no number;
   v_rowid rowid;
   /*for client_info */
   v_free_blocks number;
   v_init_ext_blocks number;
   v_reused_blocks number;
   v_total_blocks number;
   v_scaned_blocks number default 0;
   v_rows_insert number default 0;
   /* end for client_info */
begin
  select data_object_id into v_data_object_id
  from dba_objects
  where object_type = 'TABLE'
  and owner = v_table_owner
  and object_name = v_table_name;
  select tablespace_name into v_tablespace_name
  from dba_tables
  where owner = v_table_owner
  and table_name = v_table_name;
    /*for client_info */
        dbms_application_info.set_client_info('caculating v_init_ext_blocks');
select blocks into v_init_ext_blocks
  from dba_extents
  where owner=v_table_owner
    and segment_name=v_table_name
    and extent_id=0 ;
          dbms_application_info.set_client_info('caculating v_free_blocks');
  select sum(blocks) into v_free_blocks
   from dba_free_space
   where tablespace_name = v_tablespace_name;
         dbms_application_info.set_client_info('caculating v_reused_blocks');
select sum(blocks) into  v_reused_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_name
                  )
              where rn=1   ;
   v_total_blocks := v_free_blocks + v_reused_blocks + v_init_ext_blocks;
    /*end for client_info */
   for i in (select relative_fno,block_id,blocks ,'INIT EXT' as segname
               from dba_extents
              where owner=v_table_owner
                and segment_name=v_table_name
                and extent_id=0
          union all
             select relative_fno,block_id,blocks ,'FREE' segname
               from dba_free_space
              where tablespace_name = v_tablespace_name
          union all
              select relative_fno,block_id,blocks ,segname
              from (select relative_fno,block_id,blocks,owner||'.'||segment_name segname, row_number()over(partition by owner,segment_name,PARTITION_NAME order by extent_id desc) rn
                      from dba_extents
                     where tablespace_name = v_tablespace_name
                  )
              where rn=1
    ) loop
   v_file_no:=i.relative_fno;
   v_start_block_no:=i.block_id;
   v_end_block_no:=i.block_id+i.blocks-1;   
   --for client info
   v_scaned_blocks:=v_scaned_blocks+i.blocks;
       for block# in v_start_block_no .. v_end_block_no loop
        begin
         for row# in 0 .. 999 loop
           v_rowid:=dbms_rowid.rowid_create(1, v_data_object_id,v_file_no,block#,row#);
           execute immediate 'insert into '||v_new_owner||'.'||v_table_name ||
                             ' select * from '||v_table_owner||'.'||v_table_name ||
                             ' where rowid = :rid ' using v_rowid;
   --for client info
           v_rows_insert:=v_rows_insert+sql%rowcount;
        dbms_application_info.set_client_info(
          'Scaned ' || round(v_scaned_blocks/v_total_blocks*100)||'%blocks(' || v_scaned_blocks ||'/'||v_total_blocks ||'), Inserted '|| v_rows_insert||' rows, current scaning '||i.segname);
         end loop;
        exception
          when others then
            null;
            --dbms_application_info.set_client_info(sqlerrm);
        end;
        commit;        
       end loop;
    end loop;
end;
/