原理
Truncate 不会逐个清除用户数据块上的数据,而仅仅重置数据字典和元数据块上的元数据(如存储断头和扩展段图)。
也就是说,此时,基本数据表并未破坏,而是被系统回收,等待被重新分配—因此,要恢复被truncate的数据,需要及时备份所在的数据文件。
实验工具
注:本次实验使用Fy_Recover_Data恢复被TRUNCATE的数据
实验步骤
1. 下载并解压Fy_Recover_Data
[oracle@hawker ~]$ ll
total 380
-rw-r--r--. 1 oracle oinstall 79775 Mar 7 2014 FY_Recover_Data.pck
-rw-r--r--. 1 oracle oinstall 12888 May 4 17:10 FY_Recover_Data.zip
-rw-r-----. 1 oracle oinstall 289692 May 4 11:46 install2018-05-04_11-38-06.log
2.编译Fy_Recover_Data
sys@DBHAWK>@FY_Recover_Data.pck
Enter value for files:
old 30: -- 1. Temp Restore and Recover tablespace & files ---
new 30: -- 1. Temp Restore and Recover tablespace ---
Package created.
Package body created.
3.创造实验环境
scott@DBHAWK>select * from hawk_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@DBHAWK>select count(1) from hawk_dept;
COUNT(1)
----------
4
scott@DBHAWK>truncate table hawk_dept;
Table truncated.
scott@DBHAWK>select * from hawk_dept;
no rows selected
scott@DBHAWK>select count(1) from hawk_dept;
COUNT(1)
----------
0
4.使用Fy_Recover_Data恢复数据
sys@DBHAWK>exec fy_recover_data.recover_truncated_table('SCOTT','hawk_dept');
00:08:03: Use existing Directory Name: FY_DATA_DIR
00:08:03: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT
00:08:03: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT
00:08:04: Recover Table: SCOTT.HAWK_DEPT$
00:08:04: Restore Table: SCOTT.HAWK_DEPT$$
00:08:13: Copy file of Recover Tablespace: FY_REC_DATA_COPY.DAT
00:08:13: begin to recover table SCOTT.HAWK_DEPT
00:08:13: Use existing Directory Name: TMP_HF_DIR
00:08:13: Recovering data in datafile /u01/app/oracle/oradata/dbhawk/users01.dbf
00:08:13: Use existing Directory Name: TMP_HF_DIR
00:08:14: 1 truncated data blocks found.
00:08:14: 4 records recovered in backup table SCOTT.HAWK_DEPT$$
00:08:14: Total: 1 truncated data blocks found.
00:08:14: Total: 4 records recovered in backup table SCOTT.HAWK_DEPT$$
00:08:14: Recovery completed.
00:08:14: Data has been recovered to SCOTT.HAWK_DEPT$$
PL/SQL procedure successfully completed.
scott@DBHAWK>select * from HAWK_DEPT$$;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@DBHAWK>insert into hawk_dept select * from HAWK_DEPT$$;
4 rows created.
scott@DBHAWK>select * from hawk_dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
5.清理恢复程序生成的表空间
sys@DBHAWK>drop tablespace FY_REC_DATA including contents and datafiles;
Tablespace dropped.
sys@DBHAWK>drop tablespace FY_RST_DATA including contents and datafiles;
Tablespace dropped.
注:生产环境中谨慎操作,如有必要请联系专业人士处理。