合 Oracle中的ORA_ROWSCN函数的作用是什么
Tags： Oracle增量同步CDC时间戳ORA_ROWSCN变更数据捕获
👉 本文共约3109个字，系统预计阅读时间或需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 |
b. 删除一列，所有的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 drop column ename; 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 3090930 7521 1250 AAAWmYAAEAAAAE7AAC 315 3090930 7566 2975 AAAWmYAAEAAAAE7AAD 315 3090930 7654 1250 AAAWmYAAEAAAAE7AAE 315 3090930 7698 2850 AAAWmYAAEAAAAE7AAF 315 3090930 7782 2450 AAAWmYAAEAAAAE7AAG 315 3090930 7788 3000 AAAWmYAAEAAAAE7AAH 315 3090930 7839 5000 AAAWmYAAEAAAAE7AAI 315 3090930 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3090930 7876 1100 AAAWmYAAEAAAAE7AAK 315 3090930 7900 950 AAAWmYAAEAAAAE7AAL 315 3090930 7902 3000 AAAWmYAAEAAAAE7AAM 315 3090930 7934 1300 AAAWmYAAEAAAAE7AAN 315 3090930 8000 100 AAAWmYAAEAAAAE/AAB 319 3090930 8001 101 AAAWmYAAEAAAAE/AAC 319 3090930 |
可以看到新增一列，ora_rowscn并没有发生变化，删除一列，ora_rowscn发生了变化。这是因为新增一列只是修改了表的定义，对表的行没有影响，而删除一列，会同步修改表的行，所以ora_rowscn会发生变化。
查看scn对应的时间
使用scn_to_timestamp可以将scn转换成时间
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | SQL> select empno, sal, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn, scn_to_timestamp(ora_rowscn) from t1; EMPNO SAL ROWID BLOCK_ID ORA_ROWSCN SCN_TO_TIMESTAMP(ORA_ROWSCN) ---------- ---------- ------------------ ---------- ---------- ----------------------------------- 7369 100 AAAWmYAAEAAAAE7AAA 315 3090930 20-FEB-21 02.05.37.000000000 PM 7521 1250 AAAWmYAAEAAAAE7AAC 315 3090930 20-FEB-21 02.05.37.000000000 PM 7566 2975 AAAWmYAAEAAAAE7AAD 315 3090930 20-FEB-21 02.05.37.000000000 PM 7654 1250 AAAWmYAAEAAAAE7AAE 315 3090930 20-FEB-21 02.05.37.000000000 PM 7698 2850 AAAWmYAAEAAAAE7AAF 315 3090930 20-FEB-21 02.05.37.000000000 PM 7782 2450 AAAWmYAAEAAAAE7AAG 315 3090930 20-FEB-21 02.05.37.000000000 PM 7788 3000 AAAWmYAAEAAAAE7AAH 315 3090930 20-FEB-21 02.05.37.000000000 PM 7839 5000 AAAWmYAAEAAAAE7AAI 315 3090930 20-FEB-21 02.05.37.000000000 PM 7844 1500 AAAWmYAAEAAAAE7AAJ 315 3090930 20-FEB-21 02.05.37.000000000 PM 7876 1100 AAAWmYAAEAAAAE7AAK 315 3090930 20-FEB-21 02.05.37.000000000 PM 7900 950 AAAWmYAAEAAAAE7AAL 315 3090930 20-FEB-21 02.05.37.000000000 PM 7902 3000 AAAWmYAAEAAAAE7AAM 315 3090930 20-FEB-21 02.05.37.000000000 PM 7934 1300 AAAWmYAAEAAAAE7AAN 315 3090930 20-FEB-21 02.05.37.000000000 PM 8000 100 AAAWmYAAEAAAAE/AAB 319 3090930 20-FEB-21 02.05.37.000000000 PM 8001 101 AAAWmYAAEAAAAE/AAC 319 3090930 20-FEB-21 02.05.37.000000000 PM |
但是并不是所有的scn都可以使用scn_to_timestamp函数，下面的scn就报错
1 2 3 4 5 6 7 | SQL> select scn_to_timestamp(1089685) from dual; select scn_to_timestamp(1089685) from dual * ERROR at line 1: ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1 |
这是因为数据库会自动清理旧的scn，scn_to_timestamp函数能够转换成时间的最小scn记录在表smon_scn_time中，只有大于等于这个最小值才能使用scn_to_timestamp函数
1 2 3 4 5 | SQL> select min(scn) from sys.smon_scn_time; MIN(SCN) ---------- 1089686 |
基于行级别
建一张rowdependencies的表
1 2 3 4 5 6 7 8 9 10 | SQL> create table t2(id int, name varchar2(10)) rowdependencies; SQL> insert into t2 values(100, 'aaa'); SQL> insert into t2 values(200, 'ccc'); SQL> commit; SQL> select id, name, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t2; ID NAME ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 100 aaa AAAWmdAAEAAAAFcAAA 348 3094286 200 ccc AAAWmdAAEAAAAFcAAB 348 3094286 |
修改一行数据，可以看到仅仅被修改的行的ORA_ROWSCN发生了变化
1 2 3 4 5 6 7 8 | SQL> update t2 set name = 'bbb' where id = 200; SQL> commit; SQL> select id, name, rowid, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t2; ID NAME ROWID BLOCK_ID ORA_ROWSCN ---------- ---------- ------------------ ---------- ---------- 100 aaa AAAWmdAAEAAAAFcAAA 348 3094286 200 bbb AAAWmdAAEAAAAFcAAB 348 3094342 |
可以看到rowdependencies的表是细颗粒的，ora_rowscn精确到每一行，而创建表默认是norowdependencies，只精确到块。
示例2
ORA_ROWSCN函数的使用示例如下所示，
基于块级别：
1 2 3 4 5 6 7 8 9 10 11 12 13 | SYS@lhrdb> CREATE TABLE AA (COL VARCHAR2(255)); Table created. SYS@lhrdb> INSERT INTO AA (COL) VALUES ('1'); 1 row created. SYS@lhrdb> INSERT INTO AA (COL) VALUES ('2'); 1 row created. SYS@lhrdb> COMMIT; Commit complete. SYS@lhrdb> SELECT COL,TO_CHAR(ORA_ROWSCN),TO_CHAR(SCN_TO_TIMESTAMP(ORA_ROWSCN),'YYYY-MM-DD HH24:MI:SS') CHR_DATE FROM AA; COL TO_CHAR(ORA_ROWSCN) CHR_DATE -------- ----------------------------- ------------------- 1 5038627 2016-08-17 16:38:30 2 5038627 2016-08-17 16:38:30 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
