1.闪回查询，查询某个时间点的数据：
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
24-12月-07 03.27.26.790000 下午 +08:00
SQL> Select * from SCOTT.Emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7524 WARD SALESMAN 7698 22-Feb-81 1250.00 500.00 30
7569 JONES MANAGER 7839 02-Apr-81 2975.00 20
7657 MARTIN SALESMAN 7698 28-Sep-81 1250.00 1400.00 30
7701 BLAKE MANAGER 7839 01-May-81 2850.00 30
7785 CLARK MANAGER 7839 09-Jun-81 2450.00 10
7791 SCOTT ANALYST 7566 19-Apr-87 3000.00 20
7842 KING PRESIDENT 17-Nov-81 5000.00 10
7847 TURNER SALESMAN 7698 08-Sep-81 1500.00 0.00 30
7879 ADAMS CLERK 7788 23-May-87 1100.00 20
7903 JAMES CLERK 7698 03-Dec-81 950.00 30
7905 FORD ANALYST 7566 03-Dec-81 3000.00 20
7937 MILLER CLERK 7782 23-Jan-82 1300.00 10
SQL> delete from SCOTT.Emp where empno=7903;
1 row deleted
SQL> commit;
Commit complete
SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
24-12月-07 03.28.27.232000 下午 +08:00
SQL> Select * from SCOTT.Emp AS OF timestamp TO_TIMESTAMP('2007-12-24 15:27:26','YYYY-MM-DD HH24:MI:SS');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7524 WARD SALESMAN 7698 22-Feb-81 1250.00 500.00 30
7569 JONES MANAGER 7839 02-Apr-81 2975.00 20
7657 MARTIN SALESMAN 7698 28-Sep-81 1250.00 1400.00 30
7701 BLAKE MANAGER 7839 01-May-81 2850.00 30
7785 CLARK MANAGER 7839 09-Jun-81 2450.00 10
7791 SCOTT ANALYST 7566 19-Apr-87 3000.00 20
7842 KING PRESIDENT 17-Nov-81 5000.00 10
7847 TURNER SALESMAN 7698 08-Sep-81 1500.00 0.00 30
7879 ADAMS CLERK 7788 23-May-87 1100.00 20
7903 JAMES CLERK 7698 03-Dec-81 950.00 30
7905 FORD ANALYST 7566 03-Dec-81 3000.00 20
7937 MILLER CLERK 7782 23-Jan-82 1300.00 10
12 rows selected
SQL> Select * from SCOTT.Emp
2 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7524 WARD SALESMAN 7698 22-Feb-81 1250.00 500.00 30
7569 JONES MANAGER 7839 02-Apr-81 2975.00 20
7657 MARTIN SALESMAN 7698 28-Sep-81 1250.00 1400.00 30
7701 BLAKE MANAGER 7839 01-May-81 2850.00 30
7785 CLARK MANAGER 7839 09-Jun-81 2450.00 10
7791 SCOTT ANALYST 7566 19-Apr-87 3000.00 20
7842 KING PRESIDENT 17-Nov-81 5000.00 10
7847 TURNER SALESMAN 7698 08-Sep-81 1500.00 0.00 30
7879 ADAMS CLERK 7788 23-May-87 1100.00 20
7905 FORD ANALYST 7566 03-Dec-81 3000.00 20
7937 MILLER CLERK 7782 23-Jan-82 1300.00 10
11 rows selected
2.闪回版本查询,查看两个时间间隔内的一行的所有版本。
SQL> SELECT * FROM SCOTT.Emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7524 WARD SALESMAN 7698 22-Feb-81 1250.00 500.00 30
7569 JONES MANAGER 7839 02-Apr-81 2975.00 20
7657 MARTIN SALESMAN 7698 28-Sep-81 1250.00 1400.00 30
7701 BLAKE MANAGER 7839 01-May-81 2850.00 30
7785 CLARK MANAGER 7839 09-Jun-81 2450.00 10
7791 SCOTT ANALYST 7566 19-Apr-87 3000.00 20
7842 KING PRESIDENT 17-Nov-81 5000.00 10
7847 TURNER SALESMAN 7698 08-Sep-81 1500.00 0.00 30
7879 ADAMS CLERK 7788 23-May-87 1100.00 20
7905 FORD ANALYST 7566 03-Dec-81 3000.00 20
7937 MILLER CLERK 7782 23-Jan-82 1300.00 10
11 rows selected
SQL> DELETE FROM SCOTT.Emp WHERE EMPNO=7842;
1 row deleted
SQL> COMMIT;
Commit complete
SQL> Select * from SCOTT.Emp VERSIONS BETWEEN timestamp MINVALUE AND MAXVALUE;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7842 KING PRESIDENT 17-Nov-81 5000.00 10
7903 JAMES CLERK 7698 03-Dec-81 950.00 30
7524 WARD SALESMAN 7698 22-Feb-81 1250.00 500.00 30
7569 JONES MANAGER 7839 02-Apr-81 2975.00 20
7657 MARTIN SALESMAN 7698 28-Sep-81 1250.00 1400.00 30
7701 BLAKE MANAGER 7839 01-May-81 2850.00 30
7785 CLARK MANAGER 7839 09-Jun-81 2450.00 10
7791 SCOTT ANALYST 7566 19-Apr-87 3000.00 20
7842 KING PRESIDENT 17-Nov-81 5000.00 10
7847 TURNER SALESMAN 7698 08-Sep-81 1500.00 0.00 30
7879 ADAMS CLERK 7788 23-May-87 1100.00 20
7903 JAMES CLERK 7698 03-Dec-81 950.00 30
7905 FORD ANALYST 7566 03-Dec-81 3000.00 20
7937 MILLER CLERK 7782 23-Jan-82 1300.00 10
14 rows selected
3.闪回事务查询，查看事务所作的全部改变。
Select * from FLASHBACK_TRANSACTION_QUERY WHERE TABLE_NAME='';
SQL> Select * from FLASHBACK_TRANSACTION_QUERY where table_name='EMP';
XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
0100160005030000 693706 24-Dec-07 693719 24-Dec-07 SYSTEM 1 DELETE EMP SCOTT AAAL+ZAAEAAAAAdAAB insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
02000F00F2020000 693792 24-Dec-07 693797 24-Dec-07 SYSTEM 1 DELETE EMP SCOTT AAAL+ZAAEAAAAAdAAL insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
03002D001A070000 693444 24-Dec-07 693446 24-Dec-07 SYSTEM 1 DELETE EMP SCOTT AAAL+ZAAEAAAAAdAAA insert into "SCOTT"."EMP"("EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","D
*注意：ddl不能闪回。
阅读(847) | 评论(0) | 转发(0) |
