恢复truncate后的表

文档课题：恢复truncate后的表.
数据库：oracle 11.2.0.4
1、模拟异常
1.1、建测试表
[oracle@leo-oel150 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Feb 4 23:23:58 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@orcl150> create user leo identified by leo;

User created.

SYS@orcl150> grant dba,connect to leo;

Grant succeeded.

SYS@orcl150> conn leo/leo;
Connected.
LEO@orcl150> create table test as select * from dba_objects;

Table created.

LEO@orcl150> select count(*) from test;

  COUNT(*)
----------
     86389
1.2、truncate表
--使用truncate清空表.
LEO@orcl150> truncate table test;

Table truncated.

LEO@orcl150> select count(*) from test;

  COUNT(*)
----------
         0
2、恢复
2.1、准备工作
--查表所在数据文件位置.
SYS@orcl150> select file_name from dba_data_files f,dba_tables t where t.owner='LEO' and t.table_name='TEST' and t.tablespace_name=f.tablespace_name;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl150/users01.dbf
--执行脚本并输入文件位置.
SYS@orcl150> @/home/oracle/FY_Recover_Data.pck
Enter value for files: /u01/app/oracle/oradata/orcl150/users01.dbf
old  30:   --   1. Temp Restore and Recover tablespace & files                     ---
new  30:   --   1. Temp Restore and Recover tablespace /u01/app/oracle/oradata/orcl150/users01.dbf                     ---

Package created.

Package body created.
2.2、开始恢复
--执行恢复，分别输入用户名和表名.
SYS@orcl150> exec fy_recover_data.recover_truncated_table('LEO','TEST');

PL/SQL procedure successfully completed.

SYS@orcl150> conn leo/leo;
Connected.
LEO@orcl150> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE
TEST$                          TABLE
TEST$$                         TABLE

LEO@orcl150> select count(*) from leo.test$;

  COUNT(*)
----------
         0
LEO@orcl150> select count(*) from test;

  COUNT(*)
----------
         0
LEO@orcl150> select count(*) from test$$;

  COUNT(*)
----------
     86389
SYS@orcl150> insert into leo.test select * from leo.test$$;

86389 rows created.
SYS@orcl150> select count(*) from leo.test;

  COUNT(*)
----------
     86389

说明：成功恢复test表.
3、注意事项
对于使用工具fy_recover_data进行数据恢复，需确保：
a、truncate表后，需保证没有新的数据进入表中，否则无法还原；
b、存放该表的数据文件块不能被覆盖，否则无法完整还原数据.
在发生故障后，可以迅速使用：
SQL> alter tablespace users read only;
SQL> alter tablespace users read write;

关闭/开启表空间的写功能，进而保证数据文件不被覆盖.

参考网址：
https://blog.csdn.net/qq_36815190/article/details/126934932
https://www.elecfans.com/d/1910084.html