ROWDEPENDENCIES行追踪
前段时间有朋友在群里问怎样开启行级数据的追踪，也就是在表上没有时间类型的字段的时候，可以追踪行级数据的修改时间。
在10g版本及之后，可以通过ora_rowscn伪列还查询行级数据的更改，默认情况是该伪列使用NOROWDEPENDENCIES模式读取数据所在的数据块头的SCN，这样经过转换就可以查询到具体的时间，但是这种方法查询结果是不准确的，因为同一个数据块可能存放多条记录，这样查询出的结果，同一个数据块里面存放的数据的修改时间是一样的，而同一个数据块里面存放的数据的修改时间可能间隔很大，这样就可能导致查询的结果和数据真实修改时间误差较大。
当然，有NOROWDEPENDENCIES模式理论上就应该有ROWDEPENDENCIES模式，也的确存在ROWDEPENDENCIES模式，需要在建表的时候指定ROWDEPENDENCIES模式，不指定默认使用NOROWDEPENDENCIES模式。
ROWDEPENDENCIES模式会将行的修改SCN以6字节长度记录在行内，这样每一行数据都要多6字节的磁盘开销。使用ROWDEPENDENCIES模式查询数据时，不会去查询数据所在数据块头的SCN，而是直接查询记录在行内的SCN信息，所以查询出来的结果是准确的。
但是无论是NOROWDEPENDENCIES还是ROWDEPENDENCIES模式，都不是对所有数据都有效的，这受限于ORACLE内部的SCN机制，一般只能查询最近一周左右修改的数据，再早的数据可能就查询不了了。
下面是针对NOROWDEPENDENCIES和ROWDEPENDENCIES模式做的一些测试，有需求的朋友可以参考下。
首先创建测试表，T_NOROWDEPENDENCIES使用默认级别的NOROWDEPENDENCIES模式，T_ROWDEPENDENCIES使用ROWDEPENDENCIES模式。
dbdream@IVLDB> create table T_NOROWDEPENDENCIES(id number,tim date); Table created. dbdream@IVLDB> create table T_ROWDEPENDENCIES(id number,tim date) ROWDEPENDENCIES; Table created.
然后分别向这两张测试表中插入数据。
dbdream@IVLDB> insert into T_NOROWDEPENDENCIES values (1,sysdate); 1 row created. dbdream@IVLDB> commit; Commit complete. dbdream@IVLDB> insert into T_NOROWDEPENDENCIES values (2,sysdate); 1 row created. dbdream@IVLDB> commit; Commit complete. dbdream@IVLDB> insert into T_NOROWDEPENDENCIES values (3,sysdate); 1 row created. dbdream@IVLDB> commit; Commit complete. dbdream@IVLDB> insert into T_ROWDEPENDENCIES values (1,sysdate); 1 row created. dbdream@IVLDB> commit; Commit complete. dbdream@IVLDB> insert into T_ROWDEPENDENCIES values (2,sysdate); 1 row created. dbdream@IVLDB> commit; Commit complete. dbdream@IVLDB> insert into T_ROWDEPENDENCIES values (3,sysdate); 1 row created. dbdream@IVLDB> commit; Commit complete.
然后通过ora_rowscn函数查询这两张表的修改时间。
dbdream@IVLDB> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. dbdream@IVLDB> alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss'; Session altered. dbdream@IVLDB> col ORSCN for a20 dbdream@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.* from T_NOROWDEPENDENCIES t; ORSCN ID TIM -------------------- ---------- ------------------- 11:25:39 1 2017-10-20 11:25:00 11:25:39 2 2017-10-20 11:25:26 11:25:39 3 2017-10-20 11:25:37 dbdream@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.* from T_ROWDEPENDENCIES t; ORSCN ID TIM -------------------- ---------- ------------------- 11:25:53 1 2017-10-20 11:25:51 11:26:10 2 2017-10-20 11:26:10 11:26:21 3 2017-10-20 11:26:22
通过实验可以看到NOROWDEPENDENCIES模式的时候，三条测试数据的修改时间是一样的，这是因为这三条数据存在了同一个数据块里，当然也有可能查询出来的时间并不一样，因为ORACLE表的数据是随机存放的，即使数据块的空间充足，数据也不一定存放在同一个数据块里。
而ROWDEPENDENCIES模式的表，三条记录的修改时间都是不一样的，这是因为这些数据的SCN信息都存放在行内，并不是根据数据块头的SCN得来的。
下面验证下是不是所有的数据都可以使用这种方法来获取数据的修改时间，首先创建测试需要的序列和存储过程，然后创建JOB去定时执行这个存储过程，执行一段时间后，再来使用这种方法查询。
dbdream@IVLDB> create sequence SEQ_ROWDEPENDENCIES 2 minvalue 1 3 maxvalue 999999999999999999999999999 4 start with 4 5 increment by 1 6 cache 20; Sequence created. dbdream@IVLDB> create sequence SEQ_NOROWDEPENDENCIES 2 minvalue 1 3 maxvalue 999999999999999999999999999 4 start with 4 5 increment by 1 6 cache 20; Sequence created. dbdream@IVLDB> CREATE or replace PROCEDURE JOB_ROW_DEPENDENCIES AS 2 BEGIN 3 insert into T_NOROWDEPENDENCIES values (SEQ_NOROWDEPENDENCIES.NEXTVAL,sysdate); 4 insert into T_ROWDEPENDENCIES values (SEQ_ROWDEPENDENCIES.NEXTVAL,sysdate); 5 COMMIT; 6 EXCEPTION 7 WHEN OTHERS THEN 8 NULL; 9 END; 10 / Procedure created. dbdream@IVLDB> variable n number; dbdream@IVLDB> begin 2 dbms_job.submit(:n,'JOB_ROW_DEPENDENCIES;',sysdate,'sysdate+60/1440'); 3 commit; 4 end; 5 / PL/SQL procedure successfully completed.
这样，每1个小时，都会向这两张测试表中插入一条数据，大概两周后再来用这种方法来查询表的修改信息。
dbdream@IVLDB> select min(tim),max(tim) from T_ROWDEPENDENCIES;
MIN(TIM) MAX(TIM)
------------------- -------------------
2017-10-20 11:25:51 2017-11-06 17:02:16
dbdream@IVLDB> select min(tim),max(tim) from T_NOROWDEPENDENCIES;
MIN(TIM) MAX(TIM)
------------------- -------------------
2017-10-20 11:25:00 2017-11-06 17:02:16
dbdream@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.* from T_NOROWDEPENDENCIESt where t.id <=5;
ORSCN ID TIM
------------------------------- ---------- -------------------
06-NOV-17 05.02.14.000000000 PM 1 2017-10-20 11:25:00
06-NOV-17 05.02.14.000000000 PM 2 2017-10-20 11:25:26
06-NOV-17 05.02.14.000000000 PM 3 2017-10-20 11:25:37
06-NOV-17 05.01.41.000000000 AM 4 2017-10-20 11:41:39
06-NOV-17 05.01.41.000000000 AM 5 2017-10-20 12:41:43
dbdream@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.* from T_ROWDEPENDENCIES t where t.id <=5;
select scn_to_timestamp(ora_rowscn) orscn,t.* from T_ROWDEPENDENCIES t where t.id <=5
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
可见NOROWDEPENDENCIES模式的表还可以查询半个月左右修改的数据，而ROWDEPENDENCIES模式已经查询不了半个月左右修改的数据了，下面根据时间查询ROWDEPENDENCIES模式的表可以查询的时间大概是多久。
dbdream@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.* from T_ROWDEPENDENCIES t where t.tim>=sysdate-6.6 and rownum <=5;
ORSCN ID TIM
------------------------------- ---------- -------------------
31-OCT-17 09.56.31.000000000 AM 274 2017-10-31 09:56:32
31-OCT-17 12.56.36.000000000 PM 277 2017-10-31 12:56:37
31-OCT-17 01.56.36.000000000 PM 278 2017-10-31 13:56:39
31-OCT-17 06.56.45.000000000 PM 283 2017-10-31 18:56:47
31-OCT-17 10.56.56.000000000 PM 287 2017-10-31 22:56:57
dbdream@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.* from T_ROWDEPENDENCIES t where t.tim>=sysdate-6.7 and rownum <=5;
select scn_to_timestamp(ora_rowscn) orscn,t.* from T_ROWDEPENDENCIES t where t.tim>=sysdate-6.7 and rownum <=5
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
本测试环境ROWDEPENDENCIES可以查询大约6.6天之内被修改的数据，差不多一周左右。本案例没有测试出NOROWDEPENDENCIES模式的有效时间范围，网上也有贴出ORACLE的官方资料说有效时间是5天，可是本案例可以查询出半个月左右的数据，这可能和数据库的版本或者数据的修改频率有关系，毕竟这个功能是10g就开始有了，而我测试数据库版本是11g，还有就是我的测试数据库数据变化并不频繁，下面找一张测试数据库的业务表来严重下，NOROWDEPENDENCIES模式也不是所有数据都可以查询的。 下面的是我们测试环境的一张订单主表，经查询里面存放2013年8月至今天的数据，这张表一直都有数据变化，可以用来测试。
sys@IVLDB> select min(INST_DTM),max(INST_DTM) from chgshs.ord_ord_bsc_m; MIN(INST_DTM) MAX(INST_DTM) ------------------- ------------------- 2013-08-24 00:00:01 2017-11-06 17:38:36
通过DBA_TABLES或者USER_TABLES可以查询到表是否开启了追踪，查询显示这张表没有开启追踪，也就是使用的是NOROWDEPENDENCIES模式。
sys@IVLDB> select table_name,dependencies from dba_tables where owner='CHGSHS' and table_name ='ORD_ORD_BSC_M'; TABLE_NAME DEPENDEN ------------------------------ -------- ORD_ORD_BSC_M DISABLED
经过测试，只能查询近一周的数据，超过7天的数据就不能被查询了。
sys@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.INST_DTM from chgshs.ord_ord_bsc_m t where t.INST_DTM>=sysdate-6.9 and rownum<=5;
ORSCN INST_DTM
------------------------------- -------------------
31-OCT-17 11.13.12.000000000 AM 2017-10-31 10:06:37
31-OCT-17 11.13.12.000000000 AM 2017-10-31 10:08:56
31-OCT-17 03.19.24.000000000 PM 2017-10-31 10:46:47
31-OCT-17 03.19.24.000000000 PM 2017-10-31 10:48:27
31-OCT-17 10.59.45.000000000 AM 2017-10-31 10:59:45
sys@IVLDB> select scn_to_timestamp(ora_rowscn) orscn,t.INST_DTM from chgshs.ord_ord_bsc_m t where t.INST_DTM>=sysdate-7 and rownum<=5;
select scn_to_timestamp(ora_rowscn) orscn,t.INST_DTM from chgshs.ord_ord_bsc_m t where t.INST_DTM>=sysdate-7 and rownum<=5
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
ROWDEPENDENCIES模式因为需要在表中记录数据的SCN，因此只能在建表的时候指定，而无法通过ALTER TABLE命令修改。
dbdream@IVLDB> alter table T_NOROWDEPENDENCIES move tablespace users ROWDEPENDENCIES;
alter table T_NOROWDEPENDENCIES move tablespace users ROWDEPENDENCIES
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
可能有人会有疑问，如果新建的空表，里面没有数据，是否可以通过ALTER TABLE命令修改追踪信息呢。答案是肯定不行的，这不是数据的问题，而是语法就不支持这样的操作。
dbdream@IVLDB> create table t_xxx (id number);
Table created.
dbdream@IVLDB> alter table t_xxx move tablespace users ROWDEPENDENCIES;
alter table t_xxx move tablespace users ROWDEPENDENCIES
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
本实验验证了NOROWDEPENDENCIES这种默认的模式和建表时指定的ROWDEPENDENCIES模式下，都可以查询行级数据的修改时间，但是都有时间范围限制，基本上只能查询一周之内的数据，查过一周的数据可能无法支持。
老系统可能会存在表上没有时间类型字段的情况比较多一些，现在的程序员已经开始考虑时间类型字段的问题了，现在的开发人员在建表的时候基本都会考虑到时间类型的字段，甚至有的表里会同时存在多个时间类型的字段，而且用的还都是sysdate，程序员自己可能都不清楚到底该有哪个字段。同过这些时间类型的字段可以很快查询到数据的修改时间，就不需要使用这种方法去追踪了。
而且现在的程序员都习惯将数据的变化已日志表的形式存放在数据库中，我们就有一套系统，数据量只有5G左右，日志表存一个月的数据就要100多G，而且这些日志，程序员根本就不看，而且还不能删，程序员说指不定哪天就要用到了，我们只能定期的把相对老一些的数据迁移到专门存放历史数据的数据库中。
虽然添加时间类型的字段和日志表是有必要的，但也不需要过于夸张，规划好够用就行，时间类型的字段很占磁盘空间的，日志表最好不要记录到生产数据库中，应该尽量避免对生产数据库造成没必要的压力和负担。
本文固定链接: https://www.dbdream.com.cn/2017/11/rowdependencies%e8%a1%8c%e8%bf%bd%e8%b8%aa/ | 信春哥，系统稳，闭眼上线不回滚！
ROWDEPENDENCIES行追踪：等您坐沙发呢！
发表评论
