原理
Truncate 不会逐个清除用户数据块上的数据，而仅仅重置数据字典和元数据块上的元数据（如存储断头和扩展段图）。
也就是说，此时，基本数据表并未破坏，而是被系统回收，等待被重新分配—因此，要恢复被truncate的数据，需要及时备份所在的数据文件。实验工具
注：本次实验使用Fy_Recover_Data恢复被TRUNCATE的数据
实验步骤
1. 下载并解压Fy_Recover_Data
[oracle@hawker ~]$ lltotal 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.pckEnter 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 BOSTONscott@DBHAWK>select count(1) from hawk_dept; COUNT(1)---------- 4scott@DBHAWK>truncate table hawk_dept;Table truncated.scott@DBHAWK>select * from hawk_dept;no rows selectedscott@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_DIR00:08:03: Recover Tablespace: FY_REC_DATA; Data File: FY_REC_DATA.DAT00:08:03: Restore Tablespace: FY_RST_DATA; Data File: FY_RST_DATA.DAT00: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.DAT00:08:13: begin to recover table SCOTT.HAWK_DEPT00:08:13: Use existing Directory Name: TMP_HF_DIR00:08:13: Recovering data in datafile /u01/app/oracle/oradata/dbhawk/users01.dbf00:08:13: Use existing Directory Name: TMP_HF_DIR00: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 BOSTONscott@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.
注：生产环境中谨慎操作，如有必要请联系专业人士处理。