Oracle - ora_rowscn介绍

2022-12-04 13:48:11

一、概述

我们一般提起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
Oracle - ora_rowscn介绍
Oracle - ora_rowscn介绍
 
同理，将t1的数据块dump下来
Oracle - ora_rowscn介绍
可以看到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日志挖掘等方式查找，这些内容不在本文讨论范围内。

上一篇：ReactNative进阶（四十）：应用ListView实现分组列表


下一篇：安装Anaconda3及使用Spyder编写python3程序