truncate 扫描数据块恢复

总体思路： truncate后只是元数据修改了找不到数据了，但是只要还没覆盖，物理层面的数据还在，只要扫描所有的空块，找到属于这个对象的块，然后再通过 rowid 查询出数据， 将相关数据插入到新表中即可。 会丢数  [oracle@zc ~]$ sqlplus  zc/zc SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 8 09:40:47 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle.  All rights reserved. Last Successful login time: Mon Apr 28 2025 10:04:46 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL>  SQL>  SQL> show parameter name NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name      string cell_offloadgroup_name      string db_file_name_convert      string db_name      string tt db_unique_name      string tt global_names      boolean FALSE instance_name      string tt lock_name_space      string log_file_name_convert      string pdb_file_name_convert      string processor_group_name      string NAME      TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names      string tt1 本机为 19c的none pdb模式 创建测试表  和 测试表的备份表 方便后面对照 SQL> create table tt as select * from all_objects; Table created. SQL> create table tt_bak as select * from tt ; Table created. SQL> select count(*) from tt;   COUNT(*) ----------      67917 SQL> select count(*) from tt_bak;   COUNT(*) ----------      67917        truncate 表并填充数据   SQL> truncate table tt ; Table truncated. SQL> insert into tt select * from tt_bak where owner='ZC'; 11 rows created. SQL> commit ; Commit complete. SQL>  select rowid from tt; ROWID ------------------ AAAVk9AAFAAAAW9AAA AAAVk9AAFAAAAW9AAB AAAVk9AAFAAAAW9AAC AAAVk9AAFAAAAW9AAD AAAVk9AAFAAAAW9AAE AAAVk9AAFAAAAW9AAF AAAVk9AAFAAAAW9AAG AAAVk9AAFAAAAW9AAH AAAVk9AAFAAAAW9AAI AAAVk9AAFAAAAW9AAJ AAAVk9AAFAAAAW9AAK 11 rows selected.   SQL>  select get_rowid('AAAVk9AAFAAAAW9AAK') row_id from dual; ROW_ID -------------------------------------------------------------------------------- OBJECT# IS :88381 RELATIVE_FNO IS :5 BLOCK NUMBER IS :1469 ROW NUMBER IS :10   SQL> select get_rowid('AAAVk9AAFAAAAW9AAJ') row_id from dual;  ROW_ID -------------------------------------------------------------------------------- OBJECT# IS :88381 RELATIVE_FNO IS :5 BLOCK NUMBER IS :1469 ROW NUMBER IS :9            FILE# NAME ---------- ------------------------------------------------------------ 1 +DATA/TT/DATAFILE/system.279.1192719849 3 +DATA/TT/DATAFILE/sysaux.278.1192719893 4 +DATA/TT/DATAFILE/undotbs1.277.1192719919 5 +DATA/TT/DATAFILE/user001.dbf 7 +DATA/TT/DATAFILE/users.276.1192719919 truncate 前  SQL> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID  from dba_objects where object_name='TT' and owner='ZC'; OBJECT_NAME       OBJECT_ID DATA_OBJECT_ID -------------------- ---------- -------------- TT   88379 88379 truncate 后 SQL>  select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID  from dba_objects where object_name='TT' and owner='ZC'; OBJECT_NAME       OBJECT_ID DATA_OBJECT_ID -------------------- ---------- -------------- TT   88379 88381   原始数据   sys查询 SQL>   select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID  from dba_objects  AS OF TIMESTAMP TO_TIMESTAMP('2025-05-08 15:40:00', 'YYYY-MM-DD HH24:MI:SS')  where object_name='TT' and owner='ZC'; TT   88379     88379 修改obj$ 的DATAOBJ  后面构造数据用 SQL>  update (select * from obj$ where obj#=88379 and DATAOBJ#=88381) set DATAOBJ#=88379; 1 row updated. SQL> commit; Commit complete. 查看数据文件的最大块号：  select * from dba_DATA_FILES where file_id=5;     select * from dba_DATA_FILES where file_id=7;    SQL>  select * from dba_DATA_FILES where file_id=5; FILE_NAME --------------------------------------------------------------------------------    FILE_ID TABLESPACE_NAME        BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- ONLINE_ LOST_WR ------- ------- +DATA/TT/DATAFILE/user001.dbf 5 USERS     27262976    3328 AVAILABLE    5 YES 3.4360E+10    4194302   1   26214400       3200 ONLINE OFF SQL> select * from dba_DATA_FILES where file_id=7; FILE_NAME --------------------------------------------------------------------------------    FILE_ID TABLESPACE_NAME        BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- ONLINE_ LOST_WR ------- ------- +DATA/TT/DATAFILE/users.276.1192719919 7 USERS     32768000    4000 AVAILABLE    7 YES 3.4360E+10    4194302 160   31719424       3872 ONLINE OFF 查看bbed 配置，这里通过bbed 查询可以更好的区分需要扫描然后构造的块 [oracle@zc bbed]$ cat bbed.par password=blockedit spool=yes blocksize=8192 listfile=./filelist_with_numbers.txt mode=edit [oracle@zc bbed]$ cat filelist_with_numbers.txt [oracle@zc bbed]$ cat   filelist_with_numbers.txt 5 /software/user001.dbf 7 /software/users.276.1192719919 需要将数据文件拷贝到本地 扫描这个文件，将块的对象号查询出来 seq 128 1 3328 | xargs -IQ echo p /d dba 5,Q ktbbhsid.ktbbhsg1 |  bbed  PARFILE=./bbed.par  > /dev/null seq 128 1 4000 | xargs -IQ echo p /d dba 7,Q ktbbhsid.ktbbhsg1 |  bbed  PARFILE=./bbed.par  > /dev/null 看看有多少块 [oracle@zc bbed]$ grep -B1  "  88379$" log.bbd | grep ktbbhsid.ktbbhsg1 |wc -l 587 将需要扫描的块保存到文件中 grep -B1 88379$ log.bbd | grep ktbbhsid.ktbbhsg1 > scan.txt 将新建的这个表的最后一部分数据块也扫描一下 grep -B1 88381$ log.bbd | grep ktbbhsid.ktbbhsg1 >> scan.txt 如果时间很久可能有部分数据都会被分配给别的表了可以考虑下面的步骤2 创建存放要扫描的块的表  以及  存放备用数据的表 create table zc.scanblock ( file_id number,block_id number  )  tablespace sysaux; create table zc.bak_t tablespace sysaux as select * from zc.t where 0=1; 通过awk 修改相关文件，变成插入语句  awk '{print $5}' scan.txt |  sed 's/^/insert into zc.scanblock values (/;s/$/);/' > scan1.txt [oracle@zc bbed]$  head -2 scan1.txt ; tail -2 scan1.txt insert into zc.scanblock values (5,1469); insert into zc.scanblock values (5,1470); insert into zc.scanblock values (5,1471); [oracle@zc bbed]$  @scan1.txt SQL> commit; 进行恢复的存储过程： declare    v_fno number;    v_s_bno number;    v_e_bno number;    v_rowid rowid;    v_owner varchar2(100):='&&1';    v_table varchar2(100):='&&2';    v_o_owner varchar2(100):='&&3';    v_o_table varchar2(100):='&&4';    v_dataobj number;    v_sql varchar2(4000);    v_tablespace varchar2(100);    nrows number; begin    nrows:=0;    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 file_id,block_id from zc.scanblock) loop    v_fno:=i.file_id;    v_s_bno:=i.block_id;    v_e_bno:=i.block_id+1-1;        for j in v_s_bno .. v_e_bno loop         begin          for x in 0 .. 200 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;            if sql%rowcount = 1 then nrows:=nrows+1; end if;                if (mod(nrows,10000)=0) then commit; end if;          end loop;         exception           when others then             null;         end;         commit;        end loop;     end loop; end; /  alter system flush shared_pool;    alter system flush buffer_cache;    上面两步一定执行 不然可能抽不出数据来    然后执行： @trun.txt ZC TT ZC BAK_T select count(*) from ZC.BAK_T;      恢复现场   update (select * from obj$ where obj#=88379 and DATAOBJ#=88379) set DATAOBJ#=88381; commit;  alter system flush shared_pool;    alter system flush buffer_cache; 还有一种方法： 遍历表所在tablespace的free block和该表的第一个extent，以及该表空间所有segment的最后一个extent，使用dbms_rowid.rowid_create抽取数据      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; / 参考文档： http://www.minniebaby.tech/2021/10/25/truncate-table%e6%81%a2%e5%a4%8d-%e8%84%9a%e6%9c%ac/ https://blog.itpub.net/267265/viewspace-3083157/