Oracle - ora_rowscn介绍
一、概述
我们一般提起oracle中的伪列，比较常用的是rowid、rownum，本文将给大家介绍一个不常用的伪列ora_rowscn（纪录行最后一次更改的scn）。ora_rowscn的官方文档介绍（ https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ORA_ROWSCN-Pseudocolumn.html#GUID-8071AAB0-F656-4C93-B926-0BCE1439F121 ）。
在看后面的内容前，我提一个问题，怎么去查看一张表什么时候被人做了修改？如果是对表进行ddl操作，我们可以通过dba_objects中的last_ddl_time去查看，但如果是对表做dml操作（如：insert、update、delete），那又该怎么看呢？带着这个疑问，做下实验。
二、正式实验
1) 对表做dml操作
a. 创建测试表，查询原始数据，ora_rowscn为3089371
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
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
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
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
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是相同的。
2) 对表做ddl操作
a. 新增一列，ora_rowscn没有发生变化
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全部发生变化
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会发生变化。
3) 查看scn对应的时间
使用scn_to_timestamp可以将scn转换成时间
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就报错
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函数
SQL> select min(scn) from sys.smon_scn_time;
MIN(SCN)
----------
1089686
4) 建一张rowdependencies的表
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发生了变化
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，只精确到块。
判断表是否是rowdependencies，可以通过user_tables查看
SQL> select table_name, dependencies from user_tables where table_name in ('T1', 'T2');
TABLE_NAME DEPENDEN
------------------------------ --------
T2 ENABLED
T1 DISABLED
5) 查看rowdependencies的表和norowdependencies的表在存储上的区别
将t2的数据块dump下来
SQL> select id, name, rowid, dbms_rowid.rowid_relative_fno(rowid) file_id, dbms_rowid.rowid_block_number(rowid) block_id, ora_rowscn from t2;
ID NAME ROWID FILE_ID BLOCK_ID ORA_ROWSCN
---------- ---------- ------------------ ---------- ---------- ----------
100 aaa AAAWmdAAEAAAAFcAAA 4 348 3094286
200 bbb AAAWmdAAEAAAAFcAAB 4 348 3094342
SQL> alter session set tracefile_identifier='myt02';
SQL> alter system dump datafile 4 block min 348 block max 348;
在trace目录中找到该文件orcltest_ora_5998_myt02.trc
同理，将t1的数据块dump下来
可以看到rowdependencies比norowdependencies多记录了一列dscn，这个就是16进制的ora_rowscn，从实际情况来看，t2表的row 1的ora_rowscn并不是取的这里的dscn，而是block scn。
三、总结
再回答文章最开始的问题，如果想知道表在什么时候做了修改，ddl可以用dba_objects中的last_ddl_time去查看，dml通过scn_to_timestamp(max(ora_rowscn))查看。
表分rowdependencies和norowdependencies，默认是norowdependencies，不建议改成rowdependencies，因为用处并不大，同时也不清楚改了之后会带来什么样的影响。
如果还想知道历史上对表执行了哪些sql，可以通过数据库审计、v$sql、redo日志挖掘等方式查找，这些内容不在本文讨论范围内。
