oracle闪回查询恢复数据操作示例
oracle的闪回查询，可以查询提交在表空间的闪回数据，并可以还原所查询的数据，用于恢复短时间内的delele 或者 update 误操作，非常方便，缺点是只能恢复大概几小时内的数据。
1.基于时间的闪回
select * from 表名 as of timestamp (sysdate-10/1440); ---查询10分钟内的旧数据
示例：
1.查询表数据，共有14条记录
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3375.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3400.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
2.删除其中empno=7788的数据，并提交
SQL> delete from emp where empno='7788';
Warning: connection was lost and re-established
1 row deleted
SQL> commit;
Commit complete
3.再查询已经少了1条数据；
SQL> select * from emp
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3375.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
13 rows selected
4.通过闪回查询5分钟前表的数据，共14条
SQL> select * from emp as of timestamp (sysdate-5/1440);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3375.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3400.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
5.创建临时表并插入删除的数据
SQL> create table emp_old as select * from emp as of timestamp(sysdate-5/1440);
Table created
SQL> select * from emp_old;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3375.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3400.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
6.将数据从临时表恢复到正式表
SQL> insert into emp value select * from emp_old where empno='7788';
1 row inserted
SQL> commit;
Commit complete
SQL> select * from emp
2 ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3400.00 20
7369 SMITH CLERK 7902 1980/12/17 1200.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 3375.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1500.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3400.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected2.表drop闪回
FLASHBACK TABLE test1 TO BEFORE DROP;
SQL> create table test1(no number,name varchar(10));
Table created
SQL> insert into test1(no,name)values(1,'张三');
1 row inserted
SQL> drop table test1；
Table dropped
SQL> select * from test1；
select * from test1
ORA-00942: 表或视图不存在
SQL> FLASHBACK TABLE test1 TO BEFORE DROP;
Done
SQL> select * from test1
2 ;
NO NAME
---------- ----------
1 张三