Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1211581
  • 博文数量: 398
  • 博客积分: 10110
  • 博客等级: 上将
  • 技术积分: 4055
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-23 20:01
个人简介

新博客http://www.cnblogs.com/zhjh256 欢迎访问

文章分类

全部博文(398)

文章存档

2012年(1)

2011年(41)

2010年(16)

2009年(98)

2008年(142)

2007年(100)

我的朋友

分类: Oracle

2007-12-25 10:55:21

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不能闪回。
 
阅读(873) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~