SCOTT@PROD1>create table emp_bak1 as select * from emp;
Table created.
SCOTT@PROD1>select ename,job,sal from emp_bak1 where empno=7369;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
2.对sal字段进行更新,更新三次,每次增加200
第一次
SCOTT@PROD1>update emp_bak1 set sal=sal+200 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
第二次
SCOTT@PROD1>update emp_bak1 set sal=sal+200 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
第三次
SCOTT@PROD1>update emp_bak1 set sal=sal+200 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
3.查询更新三次后的值
SCOTT@PROD1>select ename,job,sal from emp_bak1 where empno=7369;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 1400
4.闪回版本查询,我们可以清晰的看到更新的每个版本
SCOTT@PROD1>select versions_xid xid,versions_startscn startscn,versions_endscn endscn,versions_operation
2 operation,ename,job,sal from emp_bak1 versions between scn minvalue and maxvalue;
XID STARTSCN ENDSCN O ENAME JOB SAL
---------------- ---------- ---------- - ---------- --------- ----------
08000B0028040000 1181229 U SMITH CLERK 1400
0200150019040000 1181222 1181229 U SMITH CLERK 1200
01001F003E030000 1181217 1181222 U SMITH CLERK 1000
1181217 SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
XID STARTSCN ENDSCN O ENAME JOB SAL
---------------- ---------- ---------- - ---------- --------- ----------
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
17 rows selected.
SCOTT@PROD1>set time on;
16:32:16 SCOTT@PROD1>update emp set sal=sal+200 where empno=7369;
1 row updated.
16:32:28 SCOTT@PROD1>commit;
Commit complete.
16:32:30 SCOTT@PROD1>update emp set sal=sal+200 where empno=7369;
1 row updated.
16:32:33 SCOTT@PROD1>commit;
Commit complete.
16:32:35 SCOTT@PROD1>update emp set sal=sal+200 where empno=7369;
1 row updated.
16:32:37 SCOTT@PROD1>commit;
Commit complete.
基于时间查询版本有疑问
16:35:32 SCOTT@PROD1>ed 101
select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,
versions_xid,ename,job,sal from emp
versions between timestamp sysdate-1/1440
and sysdate where empno=7369;
16:33:16 SCOTT@PROD1>select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,
16:33:38 2 versions_xid,ename,job,sal from emp
16:33:38 3 versions between timestamp sysdate-1/1440
16:33:38 4 and sysdate where empno=7369;
STARTTIME VERSIONS_XID ENAME JOB SAL
------------------- ---------------- ---------- --------- ----------
SMITH CLERK 1400
VERSIONS 子句不能用于:
- 外部表
- 临时表
- 固定表
- 视图
VERSIONS 不能跨 DDL 命令
段收缩操作被过滤掉
Table created.
SCOTT@PROD1>select ename,job,sal from emp_bak1 where empno=7369;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
2.对sal字段进行更新,更新三次,每次增加200
第一次
SCOTT@PROD1>update emp_bak1 set sal=sal+200 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
第二次
SCOTT@PROD1>update emp_bak1 set sal=sal+200 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
第三次
SCOTT@PROD1>update emp_bak1 set sal=sal+200 where empno=7369;
1 row updated.
SCOTT@PROD1>commit;
Commit complete.
3.查询更新三次后的值
SCOTT@PROD1>select ename,job,sal from emp_bak1 where empno=7369;
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 1400
4.闪回版本查询,我们可以清晰的看到更新的每个版本
SCOTT@PROD1>select versions_xid xid,versions_startscn startscn,versions_endscn endscn,versions_operation
2 operation,ename,job,sal from emp_bak1 versions between scn minvalue and maxvalue;
XID STARTSCN ENDSCN O ENAME JOB SAL
---------------- ---------- ---------- - ---------- --------- ----------
08000B0028040000 1181229 U SMITH CLERK 1400
0200150019040000 1181222 1181229 U SMITH CLERK 1200
01001F003E030000 1181217 1181222 U SMITH CLERK 1000
1181217 SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
XID STARTSCN ENDSCN O ENAME JOB SAL
---------------- ---------- ---------- - ---------- --------- ----------
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300
17 rows selected.
SCOTT@PROD1>set time on;
16:32:16 SCOTT@PROD1>update emp set sal=sal+200 where empno=7369;
1 row updated.
16:32:28 SCOTT@PROD1>commit;
Commit complete.
16:32:30 SCOTT@PROD1>update emp set sal=sal+200 where empno=7369;
1 row updated.
16:32:33 SCOTT@PROD1>commit;
Commit complete.
16:32:35 SCOTT@PROD1>update emp set sal=sal+200 where empno=7369;
1 row updated.
16:32:37 SCOTT@PROD1>commit;
Commit complete.
基于时间查询版本有疑问
16:35:32 SCOTT@PROD1>ed 101
select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,
versions_xid,ename,job,sal from emp
versions between timestamp sysdate-1/1440
and sysdate where empno=7369;
16:33:16 SCOTT@PROD1>select to_char(versions_starttime,'yyyy-mm-dd hh24:mi:ss') as starttime,
16:33:38 2 versions_xid,ename,job,sal from emp
16:33:38 3 versions between timestamp sysdate-1/1440
16:33:38 4 and sysdate where empno=7369;
STARTTIME VERSIONS_XID ENAME JOB SAL
------------------- ---------------- ---------- --------- ----------
SMITH CLERK 1400
附:涉及到的伪列
伪列 | 含义 |
VERSIONS_STARTSCN | 该记录操作时的 scn 或时间,如果为空,表示该行记录是在查询范围外创建的。 |
VERSIONS_ENDSCN | 该记录失效时的 scn 或时间,如果为空,说明记录当前时间在当前表内存在,或者已经被删除了,可以配合着 VERSIONS_OPERATION列来看,如果 VERSIONS_OPERATION 列值为 D,说明该列已被删除,如果该列为空,则说明记录在这段时间无操作。 |
VERSIONS_OPERATION | 对该行执行的操作: I 表示 insert, D 表示 delete, U 表示update。 |
VERSIONS_XID | 该操作的事务 ID |
闪回版本注意事项
VERSIONS 子句不能用于:
- 外部表
- 临时表
- 固定表
- 视图
VERSIONS 不能跨 DDL 命令
段收缩操作被过滤掉
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31400681/viewspace-2131222/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31400681/viewspace-2131222/