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/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69924215/viewspace-3083872/ ，如需转载，请注明出处，否则将追究法律责任。
