--ora_rowscn返回的是当前会话中对该行进行修改的最近的SCN scott@TESTDB11>select empno, ename, ora_rowscn from emp;
EMPNO ENAME ORA_ROWSCN ---------- ---------- ---------- 7369 SMITH 2583143 7499 ALLEN 2583143 7521 WARD 2583143 7566 JONES 2583143 7654 MARTIN 2583143 7698 BLAKE 2583143 7782 CLARK 2583143 7788 SCOTT 2583143 7839 KING 2583143 7844 TURNER 2583143 7876 ADAMS 2583143 7900 JAMES 2583143 7902 FORD 2583143 7934 MILLER 2583143
14 rows selected.
--查看当前的SCN号 scott@TESTDB11>select current_scn from v$database;
CURRENT_SCN ----------- 2643927
--对数据进行更新并提交 scott@TESTDB11>update emp set sal = sal * 2 where empno = 7788;
1 row updated.
scott@TESTDB11>commit;
Commit complete.
--再次查看ora_rowscn,都有所增长 scott@TESTDB11>select empno, ename, ora_rowscn from emp;
EMPNO ENAME ORA_ROWSCN ---------- ---------- ---------- 7369 SMITH 2644024 7499 ALLEN 2644024 7521 WARD 2644024 7566 JONES 2644024 7654 MARTIN 2644024 7698 BLAKE 2644024 7782 CLARK 2644024 7788 SCOTT 2644024 7839 KING 2644024 7844 TURNER 2644024 7876 ADAMS 2644024 7900 JAMES 2644024 7902 FORD 2644024 7934 MILLER 2644024
14 rows selected.
--为什么更新一个人的工资,所有行的ora_rowscn都改变了呢,原因就是这些行都在同一数据文件的相同的块中 scott@TESTDB11>select empno, ename, rowid, dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) block#, ora_rowscn from emp;
EMPNO ENAME ROWID FILE# BLOCK# ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- ---------- 7369 SMITH AAAUzoAAEAAAACVAAA 4 149 2644024 7499 ALLEN AAAUzoAAEAAAACVAAB 4 149 2644024 7521 WARD AAAUzoAAEAAAACVAAC 4 149 2644024 7566 JONES AAAUzoAAEAAAACVAAD 4 149 2644024 7654 MARTIN AAAUzoAAEAAAACVAAE 4 149 2644024 7698 BLAKE AAAUzoAAEAAAACVAAF 4 149 2644024 7782 CLARK AAAUzoAAEAAAACVAAG 4 149 2644024 7788 SCOTT AAAUzoAAEAAAACVAAH 4 149 2644024 7839 KING AAAUzoAAEAAAACVAAI 4 149 2644024 7844 TURNER AAAUzoAAEAAAACVAAJ 4 149 2644024 7876 ADAMS AAAUzoAAEAAAACVAAK 4 149 2644024 7900 JAMES AAAUzoAAEAAAACVAAL 4 149 2644024 7902 FORD AAAUzoAAEAAAACVAAM 4 149 2644024 7934 MILLER AAAUzoAAEAAAACVAAN 4 149 2644024
14 rows selected.
-- rowdepencencies选项:确定使用行级别的依赖性跟踪。表中的每一行都有一个SCN,这个SCN >= 修改该行的最后的事务的提交的SCN scott@TESTDB11>create table emp1 rowdependencies as select * from emp;
Table created. --刚创建之后,所有行的ora_rowscn都一致 scott@TESTDB11>select empno, ename, rowid, dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) block#, ora_rowscn from emp1;
EMPNO ENAME ROWID FILE# BLOCK# ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- ---------- 7369 SMITH AAAUz6AAEAAAAIzAAA 4 563 2644529 7499 ALLEN AAAUz6AAEAAAAIzAAB 4 563 2644529 7521 WARD AAAUz6AAEAAAAIzAAC 4 563 2644529 7566 JONES AAAUz6AAEAAAAIzAAD 4 563 2644529 7654 MARTIN AAAUz6AAEAAAAIzAAE 4 563 2644529 7698 BLAKE AAAUz6AAEAAAAIzAAF 4 563 2644529 7782 CLARK AAAUz6AAEAAAAIzAAG 4 563 2644529 7788 SCOTT AAAUz6AAEAAAAIzAAH 4 563 2644529 7839 KING AAAUz6AAEAAAAIzAAI 4 563 2644529 7844 TURNER AAAUz6AAEAAAAIzAAJ 4 563 2644529 7876 ADAMS AAAUz6AAEAAAAIzAAK 4 563 2644529 7900 JAMES AAAUz6AAEAAAAIzAAL 4 563 2644529 7902 FORD AAAUz6AAEAAAAIzAAM 4 563 2644529 7934 MILLER AAAUz6AAEAAAAIzAAN 4 563 2644529
14 rows selected.
scott@TESTDB11>update emp1 set sal = sal + 500 where empno = 7788;
1 row updated.
scott@TESTDB11>commit;
Commit complete. --更新一行之后,只有被更新的行的ora_rowscn与其它的不同 scott@TESTDB11>select empno, ename, rowid, dbms_rowid.rowid_relative_fno(rowid) file#, 2 dbms_rowid.rowid_block_number(rowid) block#, ora_rowscn from emp1;
EMPNO ENAME ROWID FILE# BLOCK# ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- ---------- 7369 SMITH AAAUz6AAEAAAAIzAAA 4 563 2644529 7499 ALLEN AAAUz6AAEAAAAIzAAB 4 563 2644529 7521 WARD AAAUz6AAEAAAAIzAAC 4 563 2644529 7566 JONES AAAUz6AAEAAAAIzAAD 4 563 2644529 7654 MARTIN AAAUz6AAEAAAAIzAAE 4 563 2644529 7698 BLAKE AAAUz6AAEAAAAIzAAF 4 563 2644529 7782 CLARK AAAUz6AAEAAAAIzAAG 4 563 2644529 7788 SCOTT AAAUz6AAEAAAAIzAAH 4 563 2644586 7839 KING AAAUz6AAEAAAAIzAAI 4 563 2644529 7844 TURNER AAAUz6AAEAAAAIzAAJ 4 563 2644529 7876 ADAMS AAAUz6AAEAAAAIzAAK 4 563 2644529 7900 JAMES AAAUz6AAEAAAAIzAAL 4 563 2644529 7902 FORD AAAUz6AAEAAAAIzAAM 4 563 2644529 7934 MILLER AAAUz6AAEAAAAIzAAN 4 563 2644529
14 rows selected. |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1151262/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1151262/