flashback off下delete和update表的恢复（flashback query技术)

来源：互联网发布：vscode 好用的插件编辑：程序博客网时间：2024/06/08 13:33

SQL> create table emp (id number,name varchar2(100),salary number);

Table created.

SQL> insert into emp values(1,'zhang',100);

1 row created.

SQL> insert into emp values(2,'jiao',100);

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='hh24:mi:ss';

SQL> select sysdate from dual;

SYSDATE
--------
16:04:12

SQL> delete from emp;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

SQL> select * from emp
2 as of timestamp
3 to_timestamp('2012-04-15 16:04:12','yyyy-mm-dd hh24:mi:ss');

SQL> insert into emp
2 select * from emp
3 as of timestamp
4 to_timestamp('2012-04-15 16:04:12','yyyy-mm-dd hh24:mi:ss');

2 rows created.

SQL> commit;

------------------------------------------------------------------------------------------------

update回之前指定列的恢复

update emp e1 set e1.salary=
(select salary from emp
as of timestamp
to_timestamp('2012-04-15 16:04:12','yyyy-mm-dd hh24:mi:ss') e2
where e1.id=e2.id )

commit;