背景：
关于oracle数据库中删除操作：delete、drop、truncate 。经常有技术人员误操作将数据删除。下面分别介绍此三种操作误删除后的恢复。
例举方案：
delete 删除表中特定数据。
语法：delete from tablename where …… --------基本sql语法，不过多描述。
操作演练
SYS@PROD1>conn scott/tiger
Connected.
SCOTT@PROD1>create table emp_tmp asselect * from emp;
SCOTT@PROD1>select * from emp_tmp;
可查看表中数据。
SCOTT@PROD1>delete from emp_tmp wheredeptno=7788;
SCOTT@PROD1>delete from emp_tmp whereempno=7788;
SCOTT@PROD1>select count(*) fromemp_tmp;
COUNT(*)
----------
13
解决方案：执行rollback；
SCOTT@PROD1>rollback;
Rollback complete.
SCOTT@PROD1>select count(*) fromemp_tmp;
COUNT(*)
----------
14
SCOTT@PROD1>
*********你懂的**********
先说drop 因为相比truncate而言，drop某张表后立即恢复的话相对简单（非drop purge），(drop某张表，例如：drop table emp;此操作将不会立即将emp表的结构及数据全部清除。只是会给emp表打个标记，此标记代表着emp已经被删除。除非通过特殊的手段查看---回收站。)
操作演练：
SCOTT@PROD1>droptable emp_tmp;
Tabledropped.
SCOTT@PROD1>select* from emp_tmp;
select *from emp_tmp
*
ERROR atline 1:
ORA-00942: table or view does notexist
SCOTT@PROD1>showrecyclebin;
ORIGINAL NAME RECYCLEBINNAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP_TMP BIN$kU9UpPC2IRjgUwsCAMAzDQ==$0 TABLE 2019-08-30:12:48:17
T BIN$kSPSWOVzEbrgUwsCAMDsdQ==$0 TABLE 2019-08-28:10:05:22
TEST2 BIN$kRECU/oVCtPgUwsCAMAMrg==$0 TABLE 2019-08-27:10:27:08
恢复操作：
方法一：
SCOTT@PROD1>select * from"BIN$kU9UpPC2IRjgUwsCAMAzDQ==$0";
SCOTT@PROD1>create table emp_tmp as select * from"BIN$kU9UpPC2IRjgUwsCAMAzDQ==$0";
Tablecreated.
SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
14
方法二：使用flashback
SCOTT@PROD1>drop table emp_tmp;
Table dropped.
SCOTT@PROD1>select count(*) fromemp_tmp;
select count(*) from emp_tmp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@PROD1>flashback table emp_tmp tobefore drop;
Flashback complete.
SCOTT@PROD1>select count(*) fromemp_tmp;
COUNT(*)
----------
14
模拟环境:
1^: SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
56
SCOTT@PROD1>truncatetable emp_tmp;
Table truncated.
SCOTT@PROD1>selectcount(*) from emp_tmp;
COUNT(*)
----------
0
2^:建立存储过程(执行配套sql 语句 )
链接：https://pan.baidu.com/s/1Ka90PGwytCR0JL5e139j8g
提取码：kpv2
[oracle@edbjr2p1~]$ sqlplus as sysdba
SQL*Plus:Release 11.2.0.3.0 Production on Fri Aug 30 15:58:55 2019
Copyright (c)1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning,OLAP, Data Mining and Real Application Testing options
SYS@PROD1>@FY_Recover_Data.pck
Enter value forfiles: ------------（此处直接回车即可）
old 30: -- 1. Temp Restore and Recovertablespace & files ---
new 30: -- 1. Temp Restore andRecover tablespace ---
Package created.
Package bodycreated.
SYS@PROD1>
3^:执行语句恢复表。
exec fy_recover_data.recover_truncated_table('SCOTT','EMP_TMP');
恢复会在原表的基础上加 $$ 符号
如果表数据量过大，时间将较长。
SCOTT@PROD1>conn/ as sysdba
Connected.
SYS@PROD1>execfy_recover_data.recover_truncated_table('SCOTT','EMP_TMP');
PL/SQL proceduresuccessfully completed.
SYS@PROD1>insertinto emp_tmp select * from emp_tmp$$;
insert intoemp_tmp select * from emp_tmp$$ *
ERROR at line 1:
ORA-00942: tableor view does not exist
SYS@PROD1>connscott/tiger
Connected.
SCOTT@PROD1>insertinto emp_tmp select * from emp_tmp$$;
56 rows created.
SCOTT@PROD1>
4^:删除临时表空间即可------（执行存储过程的时候将会产生两个临时表空间）
SCOTT@PROD1>conn/ as sysdba
Connected.
SYS@PROD1>selecttablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
FY_REC_DATA
FY_RST_DATA
SYS@PROD1>droptablespace FY_REC_DATA including contents and datafiles;
Tablespacedropped.
SYS@PROD1>droptablespace FY_RST_DATA including contents and datafiles;
Tablespacedropped.
SYS@PROD1>
恢复完毕！！！
