truncate表恢复测试

创建测试表，truncate

SQL> conn t1/t1
Connected.

SQL> create table t4 as select * from user_objects;

Table created.

SQL> truncate table t1.t4;

Table truncated.

停止业务对表空间的操作，可以将表空间readonly，收集表的元数据，索引等信息略。

查看对象信息

SQL> conn / as sysdba
Connected.
SQL> select owner,object_name from dba_objects where object_name='T4' and owner='T1';

OWNER                          OBJECT_NAME                    
------------------------------ ------------------------------ 
T1                             T4                                      

查询最后一次ddl时间

SQL> select owner,object_name,data_object_id,to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from dba_objects where object_name='T4' and owner='T1';

OWNER                          OBJECT_NAME          DATA_OBJECT_ID TO_CHAR(LAST_DDL_TI
------------------------------ -------------------- -------------- -------------------
T1                             T4                            87205 2020-03-17 12:46:40

查询时间点对应的data_object_id

SQL> SELECT obj#,OWNER#,dataobj# FROM obj$ AS OF TIMESTAMP TO_TIMESTAMP('2020-03-17 12:46:40', 'YYYY-MM-DD HH24:MI:SS') WHERE NAME = 'T4';

      OBJ#     OWNER#   DATAOBJ#
---------- ---------- ----------
     87204         83      87204

创建新的表空间防止数据被覆盖

SQL> create tablespace test datafile size 100m;

Tablespace created.

SQL> create table t4 tablespace test  as select * from t1.t4 where rownum<1;

Table created.

修改dataobj#

SQL> update obj$ set dataobj#=87204 where obj#=87204;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

查看表已显示object no longer exists

SQL> select * from t1.t4
  2  ;
select * from t1.t4
                 *
ERROR at line 1:
ORA-08103: object no longer exists

恢复数据到新建的表

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='T1' and segment_name='T4' and extent_id=0 
             union all
             select relative_fno,block_id,blocks 
               from dba_free_space 
              where tablespace_name in 
                          (select tablespace_name 
                             from dba_tables 
                                where owner='T1' and table_name='T4')
                         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='T1' and table_name='T4') 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,87204,v_fno,j,x);
           insert into sys.T4 select * from T1.T4 where rowid=v_rowid;
         end loop;
        exception 
          when others then 
            null;
        end;
        commit;
       end loop;           
    end loop;
end;
/

查看数据

SQL> select * from t4;

OBJECT_NAME          SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED   LAST_DDL_ TIMESTAMP        STATUS  T G S  NAMESPACE EDITION_NAME
-------------------- ------------------------------ ---------- -------------- ------------------- --------- --------- ------------------- ------- - - - ---------- ------------------------------
T4                                                       87204          87204 TABLE               17-MAR-20 17-MAR-20 2020-03-17:12:46:14 VALID   N N N        1
T11                                                      87190          87190 TABLE               17-MAR-20 17-MAR-20 2020-03-17:11:47:11 VALID   N N N        1
T3                                                       87203          87203 TABLE               17-MAR-20 17-MAR-20 2020-03-17:12:45:25 VALID   N N N        1

将dataobj#改回原值时插入也一样报错

SQL> update obj$ set dataobj#=87205 where obj#=87204;

1 row updated

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t1.t4;

no rows selected

SQL>  insert into t1.t4 select * from t4;
 insert into t1.t4 select * from t4
                *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktspfmdb:objdchk_kcbnew_3], [0], [87205], [4], [], [], [], [], [], [], [], []

删除原表重建备份表结构插入数据重建索引

SQL> drop table t1.t4 purge;

Table dropped.

SQL> create table t1.t4;

Table created.

SQL> insert into t1.t4 select * from t4;

附：

DBMS_ROWID.ROWID_CREATE (
    rowid_type           IN NUMBER,
    object_number    IN NUMBER,
    relative_fno          IN NUMBER,
    block_number     IN NUMBER,
    row_number       IN NUMBER)

参数：
rowid_type：rowid类型(restricted或者extended)。设置rowid_type为0时，代表restricted ROWID（此时，将忽略参数object_number）：设置rowid_type为1时，代表extended ROWID。
object_number：数据对象编号（仅restricted类型rowid可用）。
relative_fno：所在数据文件编号。
block_number：该数据文件中的数据块编号。
row_number：在该块中的行编号。