合 Oracle中的ORA_ROWSCN函数的作用是什么
Tags： Oracle增量同步CDC时间戳ORA_ROWSCN变更数据捕获
👉 本文共约3177个字，系统预计阅读时间或需12分钟。
目录
简介
对于每一行数据，ORA_ROWSCN可以返回每一行最近被修改的大概时间，可用于查询表最后一次被执行DML操作的时间。是每个表自带的一个伪列。
由于Oracle通过事务提交对行所在数据块来进行SCN（System Change Number，系统改变号，一个由系统内部维护的序列号。当系统需要更新的时候自动增加，它是系统中维持数据的一致性和顺序恢复的重要标志）的跟踪，即该块上有任何一行进行了修改，该块的ora_rowscn都发生变化，而且一个块的ora_rowscn是相同的。所以，它不精确。可以通过在创建表时使用行级别的依赖跟踪（ROWDEPENDENCIES）来获得一个更加精确的SCN。
在对视图进行查询时，不能使用ORA_ROWSCN函数，但对于视图的基表是可以使用ORA_ROWSCN函数的，也能在UPDATE或DELETE语句的WHERE子句中使用ORA_ROWSCN函数。
ORA_ROWSCN虽然不能用于闪回查询，但是可以用闪回版本查询来代替ORA_ROWSCN。
ORA_ROWSCN也不能用于外部表。
示例1
基于块级别
对表做dml操作
a. 创建测试表，查询原始数据，ora_rowscn为3089371
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SQL> create table t1 as select * from emp; SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 7369 800 AAAWmYAAEAAAAE7AAA 315 3089371 7499 1600 AAAWmYAAEAAAAE7AAB 315 3089371 7521 1250 AAAWmYAAEAAAAE7AAC 315 3089371 7566 2975 AAAWmYAAEAAAAE7AAD 315 3089371 7654 1250 AAAWmYAAEAAAAE7AAE 315 3089371 7698 2850 AAAWmYAAEAAAAE7AAF 315 3089371 7782 2450 AAAWmYAAEAAAAE7AAG 315 3089371 7788 3000 AAAWmYAAEAAAAE7AAH 315 3089371 7839 5000 AAAWmYAAEAAAAE7AAI 315 3089371 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3089371 7876 1100 AAAWmYAAEAAAAE7AAK 315 3089371 7900 950 AAAWmYAAEAAAAE7AAL 315 3089371 7902 3000 AAAWmYAAEAAAAE7AAM 315 3089371 7934 1300 AAAWmYAAEAAAAE7AAN 315 3089371 |
b. 修改一行数据，ora_rowscn变成3089858
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> update t1 set sal = 100 where empno = 7369; SQL> commit; SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 7369 100 AAAWmYAAEAAAAE7AAA 315 3089858 7499 1600 AAAWmYAAEAAAAE7AAB 315 3089858 7521 1250 AAAWmYAAEAAAAE7AAC 315 3089858 7566 2975 AAAWmYAAEAAAAE7AAD 315 3089858 7654 1250 AAAWmYAAEAAAAE7AAE 315 3089858 7698 2850 AAAWmYAAEAAAAE7AAF 315 3089858 7782 2450 AAAWmYAAEAAAAE7AAG 315 3089858 7788 3000 AAAWmYAAEAAAAE7AAH 315 3089858 7839 5000 AAAWmYAAEAAAAE7AAI 315 3089858 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3089858 7876 1100 AAAWmYAAEAAAAE7AAK 315 3089858 7900 950 AAAWmYAAEAAAAE7AAL 315 3089858 7902 3000 AAAWmYAAEAAAAE7AAM 315 3089858 7934 1300 AAAWmYAAEAAAAE7AAN 315 3089858 |
c. 删除一行数据，ora_rowscn变成3089961
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> delete t1 where empno = 7499; SQL> commit; SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 7369 100 AAAWmYAAEAAAAE7AAA 315 3089961 7521 1250 AAAWmYAAEAAAAE7AAC 315 3089961 7566 2975 AAAWmYAAEAAAAE7AAD 315 3089961 7654 1250 AAAWmYAAEAAAAE7AAE 315 3089961 7698 2850 AAAWmYAAEAAAAE7AAF 315 3089961 7782 2450 AAAWmYAAEAAAAE7AAG 315 3089961 7788 3000 AAAWmYAAEAAAAE7AAH 315 3089961 7839 5000 AAAWmYAAEAAAAE7AAI 315 3089961 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3089961 7876 1100 AAAWmYAAEAAAAE7AAK 315 3089961 7900 950 AAAWmYAAEAAAAE7AAL 315 3089961 7902 3000 AAAWmYAAEAAAAE7AAM 315 3089961 7934 1300 AAAWmYAAEAAAAE7AAN 315 3089961 |
d. 插入一行数据，老块（BLOCK_ID 315）的ora_rowscn仍然是3089961，新插入的行（BLOCK_ID 319）ora_rowscn是3090014
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> insert into t1 (empno, sal) values(8000, 100); SQL> commit; SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 7369 100 AAAWmYAAEAAAAE7AAA 315 3089961 7521 1250 AAAWmYAAEAAAAE7AAC 315 3089961 7566 2975 AAAWmYAAEAAAAE7AAD 315 3089961 7654 1250 AAAWmYAAEAAAAE7AAE 315 3089961 7698 2850 AAAWmYAAEAAAAE7AAF 315 3089961 7782 2450 AAAWmYAAEAAAAE7AAG 315 3089961 7788 3000 AAAWmYAAEAAAAE7AAH 315 3089961 7839 5000 AAAWmYAAEAAAAE7AAI 315 3089961 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3089961 7876 1100 AAAWmYAAEAAAAE7AAK 315 3089961 7900 950 AAAWmYAAEAAAAE7AAL 315 3089961 7902 3000 AAAWmYAAEAAAAE7AAM 315 3089961 7934 1300 AAAWmYAAEAAAAE7AAN 315 3089961 8000 100 AAAWmYAAEAAAAE/AAB 319 3090014 |
e. 再插入一行数据，老块（BLOCK_ID 315）的ora_rowscn不变，新插入的行（BLOCK_ID 319）ora_rowscn变成3090087
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SQL> insert into t1 (empno, sal) values(8001, 101); SQL> commit; SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 7369 100 AAAWmYAAEAAAAE7AAA 315 3089961 7521 1250 AAAWmYAAEAAAAE7AAC 315 3089961 7566 2975 AAAWmYAAEAAAAE7AAD 315 3089961 7654 1250 AAAWmYAAEAAAAE7AAE 315 3089961 7698 2850 AAAWmYAAEAAAAE7AAF 315 3089961 7782 2450 AAAWmYAAEAAAAE7AAG 315 3089961 7788 3000 AAAWmYAAEAAAAE7AAH 315 3089961 7839 5000 AAAWmYAAEAAAAE7AAI 315 3089961 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3089961 7876 1100 AAAWmYAAEAAAAE7AAK 315 3089961 7900 950 AAAWmYAAEAAAAE7AAL 315 3089961 7902 3000 AAAWmYAAEAAAAE7AAM 315 3089961 7934 1300 AAAWmYAAEAAAAE7AAN 315 3089961 8000 100 AAAWmYAAEAAAAE/AAB 319 3090087 8001 101 AAAWmYAAEAAAAE/AAC 319 3090087 |
可以看到ora_rowscn记录的是块级别的改动，即该块上有任何一行进行了修改，该块的ora_rowscn都发生变化，而且一个块的ora_rowscn是相同的。
对表做ddl操作
a. 新增一列，ora_rowscn没有发生变化
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> alter table t1 add test int; SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 7369 100 AAAWmYAAEAAAAE7AAA 315 3089961 7521 1250 AAAWmYAAEAAAAE7AAC 315 3089961 7566 2975 AAAWmYAAEAAAAE7AAD 315 3089961 7654 1250 AAAWmYAAEAAAAE7AAE 315 3089961 7698 2850 AAAWmYAAEAAAAE7AAF 315 3089961 7782 2450 AAAWmYAAEAAAAE7AAG 315 3089961 7788 3000 AAAWmYAAEAAAAE7AAH 315 3089961 7839 5000 AAAWmYAAEAAAAE7AAI 315 3089961 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3089961 7876 1100 AAAWmYAAEAAAAE7AAK 315 3089961 7900 950 AAAWmYAAEAAAAE7AAL 315 3089961 7902 3000 AAAWmYAAEAAAAE7AAM 315 3089961 7934 1300 AAAWmYAAEAAAAE7AAN 315 3089961 8000 100 AAAWmYAAEAAAAE/AAB 319 3090087 8001 101 AAAWmYAAEAAAAE/AAC 319 3090087 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
