查询闪回
使用pl/sql中的dbms_flashback包
-----------------------时间范围闪回----------------------------------
1.授权
grant execute on sys.dbms_flashback to scott;
2.查询emp表记录
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER sooo 7782 1982/1/23 1300.00 10
3.修改sooo为sony并commit
UPDATE emp SET job='sony' WHERE empno=7934;
COMMIT;
4.启动闪回
SQL>EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME(SYSDATE-10/1440); #24*60分钟=1440(一天),10代表10分钟之内。
SQL>SELECT * FROM EMP;
SQL>
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER sony 7782 1982/1/23 1300.00 10
5.禁用闪回
SQL>EXECUTE DBMS_FLASHBACK.DISABLE();
-----------------------------------SCN闪回-----------------------------------------------
1.定义变量保持当前scn号
SQL> VARIABLE current_scn NUMBER
SQL> EXECUTE :current_scn:=dbms_flashback.get_system_change_number();
PL/SQL procedure successfully completed
current_scn
---------
81497749
2.修改emp表
SQL> update emp set job='som' where empno=7934;
SQL>COMMIT;
3.闪回查询
SQL> EXECUTE DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER(:current_scn);
4.查询结果,关闭闪回
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7934 MILLER sooo 7782 1982/1/23 1300.00 10
SQL>DBMS_FLASHBACK.DISABLE();