--oracle 闪回查询可以做到: --假设当前距离删除数据已经有5 分钟左右的话: select * from table_name as of timestamp sysdate-5/1440;
--或者你知道你未修改前的一个时间点,假设是'2010-11-30 10:24:34' select * from table_name as of timestamp to_timestamp('2010-11-30 10:24:34','YYYY-MM-DD hh24:mi:ss');--如果要恢复之前的数据: create table tmp as select * from A as of timestamp to_timestamp('2010-11-30 10:24:34','YYYY-MM-DD hh24:mi:ss'); drop table A; alter table tmp rename to A;
--9i或以上版本支持 --select * from table_name as of timestamp to_date() 例如: SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 60 dfsdf we 70 jiujiang 50 NANCHANG 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON已选择7行。 SQL> set time on; 20:14:39 SQL> update dept set loc='test';已更新7行。已用时间: 00: 00: 00.03 20:14:52 SQL> commit;提交完成。已用时间: 00: 00: 00.01 20:14:54 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 60 dfsdf test 70 jiujiang test 50 NANCHANG test 10 ACCOUNTING test 20 RESEARCH test 30 SALES test 40 OPERATIONS test已选择7行。已用时间: 00: 00: 00.07 20:15:01 SQL> select * from dept as of timestamp to_date('20101130 20:14:39','yyyymmdd hh24:mi:ss'); DEPTNO DNAME LOC ---------- -------------- ------------- 60 dfsdf we 70 jiujiang 50 NANCHANG 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON已选择7行。已用时间: 00: 00: 00.21
--恢复数据 20:23:34 SQL> alter table dept enable row movement; --允许数据库移动列表已更改。已用时间: 00: 00: 00.95 20:23:46 SQL> flashback table dept to timestamp to_date('20101130 20:14:39','yyyymmdd hh24:mi:ss');闪回完成。已用时间: 00: 00: 07.75 20:24:09 SQL> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 60 dfsdf we 70 jiujiang 50 NANCHANG 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON已选择7行。已用时间: 00: 00: 00.11
--oracle 闪回查询可以做到:
--假设当前距离删除数据已经有5 分钟左右的话:
select * from table_name as of timestamp sysdate-5/1440;
--或者你知道你未修改前的一个时间点,假设是'2010-11-30 10:24:34'
select * from table_name as of timestamp to_timestamp('2010-11-30 10:24:34','YYYY-MM-DD hh24:mi:ss');--如果要恢复之前的数据:
create table tmp as select * from A as of timestamp to_timestamp('2010-11-30 10:24:34','YYYY-MM-DD hh24:mi:ss');
drop table A;
alter table tmp rename to A;
--select * from table_name as of timestamp to_date() 例如:
SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
60 dfsdf we
70 jiujiang
50 NANCHANG
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON已选择7行。
SQL> set time on;
20:14:39 SQL> update dept set loc='test';已更新7行。已用时间: 00: 00: 00.03
20:14:52 SQL> commit;提交完成。已用时间: 00: 00: 00.01
20:14:54 SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
60 dfsdf test
70 jiujiang test
50 NANCHANG test
10 ACCOUNTING test
20 RESEARCH test
30 SALES test
40 OPERATIONS test已选择7行。已用时间: 00: 00: 00.07
20:15:01 SQL> select * from dept as of timestamp to_date('20101130 20:14:39','yyyymmdd hh24:mi:ss');
DEPTNO DNAME LOC
---------- -------------- -------------
60 dfsdf we
70 jiujiang
50 NANCHANG
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON已选择7行。已用时间: 00: 00: 00.21
20:23:34 SQL> alter table dept enable row movement; --允许数据库移动列表已更改。已用时间: 00: 00: 00.95
20:23:46 SQL> flashback table dept to timestamp to_date('20101130 20:14:39','yyyymmdd hh24:mi:ss');闪回完成。已用时间: 00: 00: 07.75
20:24:09 SQL> select * from dept; DEPTNO DNAME LOC
---------- -------------- -------------
60 dfsdf we
70 jiujiang
50 NANCHANG
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON已选择7行。已用时间: 00: 00: 00.11