AI 助理
备案控制台
开发者社区 数据库 文章 正文

关于获取oracle中数据变更的时间戳的探索（待更新）

2018-03-01 7449
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 近期要上一个BI的项目，需要对各个业务系统内的数据进行分析，那么问题就来了。现有的数据库中有新insert进来的数据，还会有对于已有的数据进行变更的操作。那么这些新增以及变化都要反应到BI系统中。 可以现有的生产数据库的table中没有可以识别变更的时间戳字段。

近期要上一个BI的项目，需要对各个业务系统内的数据进行分析，那么问题就来了。现有的数据库中有新insert进来的数据，还会有对于已有的数据进行变更的操作。那么这些新增以及变化都要反应到BI系统中。

可以现有的生产数据库的table中没有可以识别变更的时间戳字段。如果对已有的表增加一个timestmap字段，在数据库层面上当然是非常容易办到的，一个简单的 "alter table add xxx column... " 即可。但是在业务程序方面将会带来巨大的改造量。

  • insert的时候需要多增加一个timestmap字段
  • update的时候需要同时更新timestmap字段

(这样的话，开发人员该疯了)

在MySQL数据库下可以添加一个会自动更新的timestamp。（oracle中不支持该语法）

alter table  t2   add COLUMN T_MODIFY_TM  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT  'is_timestamp';

在oracle怎么办呢？

伪列ORA_ROWSCN

想啊想，oracle既然存在rownum , rowid 这样的伪列，会不会还有行的scn的伪列呢。。果然oracle有 ---- ORA_ROWSCN

SQL> select * from t2;

        ID
----------
         0



SQL> select ora_rowscn , id from t2;

ORA_ROWSCN         ID
---------- ----------
   3489228          0


SQL> insert into t2 values (2);

1 row created.

SQL> commit;

Commit complete.

SQL>  select ora_rowscn , id from t2;

ORA_ROWSCN         ID
---------- ----------
   3489228          0
   3489004          2

为了更方便阅读，还可以把scn的数字转换为时间

SQL> select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from t2;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-26 10:40:28          0
2018-02-28 10:35:45          2

Scn转换成时间：

select to_char(scn_to_timestamp(3489228),'YYYY-MM-DD HH24:MI:SS') from dual;

时间转换成scn：

select timestamp_to_scn(to_date('2011-04-14 11:10:25','YYYY-MM-DD HH24:MI:SS')) from dual;

简单的测试，结果很令人开心哈，毕竟不用改程序了。（我为人人做贡献的DBA）

开始在业务测试库中进行验证。

问题1： 遇到了 ORA-08181

ORA-08181: 指定的编号不是有效的系统更改号

ORA-06512: 在 "SYS.SCN_TO_TIMESTAMP", line 1

  1. 00000 - "specified number is not a valid system change number"
    *Cause: supplied scn was beyond the bounds of a valid scn.

*Action: use a valid scn.

刚一到测试系统就遇到了问题。

select  to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') ,acceptdate , t.* from policycopymain t

原因： 是因为ORA_ROWSCN是一个伪列，这伪列的存在依赖于建表时使用行记录时间跟踪的参数rowdenpendies。
select min(scn) from sys.smon_scn_time ;而min(scn) 最多只能五天内的。因为SMON_SCN_TIME记录5天的数据。

问题2：ora_rowscn不是很准

当我又连续的插入了几行数据的时候，出现了更新一个数据但是好多数据的ora_rowscn都变化为一样的了。

SQL> insert into t2 values (4);

1 row created.

SQL> insert into t2 values (5);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from t2;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-26 10:40:28          0
2018-02-28 15:16:18          3
2018-02-28 15:16:18          4
2018-02-28 15:16:18          5


SQL> update t2 set id=6 where id=3;

1 row updated.

SQL> select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from t2;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-28 10:40:28          0
2018-02-28 15:16:18          6
2018-02-28 15:16:18          4
2018-02-28 15:16:18          5

SQL>  update t2 set id=7 where id=4;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from t2;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-26 10:40:28          0
2018-02-28 15:17:18          6
2018-02-28 15:17:18          7
2018-02-28 15:17:18          5

原因
ora_rowscn分为两种模式:

  • 一种是基于block这是默认的模式(块级跟踪,非行依赖性(NOROWDEPENDENCIES))；
  • 一种是基于row上，这种模式只能在建里表时指定ROWDEPENDENCIES(行级跟踪)。

不可以通过后期的alter table语句来将表修改为ROWDEPENDENCIES。

测试

SQL> create table td (id number) rowdependencies;

Table created.


SQL> insert into td values(1);

1 row created.

SQL> insert into td values (2);

1 row created.

SQL> commit;

Commit complete.

SQL>  select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from td;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-28 15:19:46          1
2018-02-28 15:19:46          2

SQL> update td set id=3 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from td;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-28 15:20:07          3
2018-02-28 15:19:46          2

SQL> insert into td values (4);

1 row created.

SQL> insert into td values (5);

1 row created.

SQL> commit;

Commit complete.

SQL>  select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from td;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-28 15:20:07          3
2018-02-28 15:19:46          2
2018-02-28 16:16:09          4
2018-02-28 16:16:09          5

SQL> 
SQL> update td set id=10 where id=4;

1 row updated.

SQL> commit;

Commit complete.

SQL>  select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS') , id from td;

TO_CHAR(SCN_TO_TIME         ID
------------------- ----------
2018-02-28 15:20:07          3
2018-02-28 15:19:46          2
2018-02-28 16:16:48         10
2018-02-28 16:16:09          5

在创建表的时候添加了ROWDEPENDENCIES属性还是非常好使的。那

我们知道，每个Block在头部是记录了该block最近事务的SCN的，所以默认情况下，只需要从block头部直接获取这个值就可以了，不需要其他任何的开销，Oracle就能做到这一点。但是这明显是不精确的，一个block中会有很多行记录，每次事务不可能影响到整个block中所有的行，所以这是一个非常不精准的估算值，同一个block的所有记录的ORA_ROWSCN都会是相同的，基本上没有多大的使用价值。

问题3 ROWDEPENDENCIES会对数据库带来多少额外的性能影响呢

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/s01/10gdb/admin/clinica/udump/clinica_ora_12934.trc

block_row_dump:
tab 0, row 0, @0x1f88
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.0351b8bd
col  0: [ 2]  c1 02
col  1: [11]  78 6f 01 02 15 20 1a 21 d8 52 68
tab 0, row 1, @0x1f70
tl: 24 fb: --H-FL-- lb: 0x0  cc: 2
dscn 0x0000.0351b8c4
col  0: [ 2]  c1 03
col  1: [11]  78 6f 01 02 15 20 26 02 ab c2 f8

/* 可以从block dump中看到每行都多出了dscn信息，这就是基于行追踪的行级ORA_ROWSCN信息的来源。
注意这里的dscn需要占用6个字节的空间，换而言之启用ROWDEPENDENCIES会为每一行多出6个字节的磁盘开销。
/ 此部分摘自 MACLEAN LIU
http://www.askmaclean.com/archives/%E5%9F%BA%E4%BA%8E%E8%A1%8C%E8%B7%9F%E8%B8%AA%E7%9A%84rowdependencies-ora_rowscn%E4%BF%A1%E6%81%AF.html

那么1千万行的数据，将会有10m存储容量的开销

文章标签：
关系型数据库
数据库
Oracle
BI
数据库管理
MySQL
关键词：
Oracle数据库一键上云数据
Oracle数据库一键上云变更
Oracle数据库一键上云数据变更
Oracle数据库一键上云时间戳
Oracle数据库一键上云探索
ora_果酱o
目录
相关文章
土木林森
|
4月前
|
SQL 运维 Oracle
【迁移秘籍揭晓】ADB如何助你一臂之力，轻松玩转Oracle至ADB的数据大转移？
【8月更文挑战第27天】ADB（Autonomous Database）是由甲骨文公司推出的自动化的数据库服务，它极大简化了数据库的运维工作。在从传统Oracle数据库升级至ADB的过程中，数据迁移至关重要。
土木林森
78 0
赵渝强老师
|
1月前
|
存储 Oracle 关系型数据库
【赵渝强老师】Oracle的还原数据
Oracle数据库中的还原数据（也称为undo数据或撤销数据）存储在还原表空间中，主要用于支持查询的一致性读取、实现闪回技术和恢复失败的事务。文章通过示例详细介绍了还原数据的工作原理和应用场景。
赵渝强老师
42 2
【赵渝强老师】Oracle的还原数据
三分钟热度的鱼
|
4月前
|
数据采集 Oracle 关系型数据库
实时计算 Flink版产品使用问题之怎么实现从Oracle数据库读取多个表并将数据写入到Iceberg表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
75 0
赵渝强老师
|
1月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的联机重做日志文件与数据写入过程
在Oracle数据库中，联机重做日志文件记录了数据库的变化，用于实例恢复。每个数据库有多组联机重做日志，每组建议至少有两个成员。通过SQL语句可查看日志文件信息。视频讲解和示意图进一步解释了这一过程。
赵渝强老师
34 0
赵渝强老师
|
1月前
|
SQL Oracle 关系型数据库
【赵渝强老师】Oracle的数据文件
在Oracle数据库中，数据库由多个表空间组成，每个表空间包含多个数据文件。数据文件存储实际的数据库数据。查询时，如果内存中没有所需数据，Oracle会从数据文件中读取并加载到内存。可通过SQL语句查看和管理数据文件。附有视频讲解及示例。
赵渝强老师
31 0
木子0204
|
2月前
|
Oracle 关系型数据库 数据库
oracle数据创建同义词
oracle数据创建同义词
木子0204
52 0
pbeskoyq7ffm4
|
4月前
|
SQL 监控 Oracle
Oracle数据误删不用怕，跟我来学日志挖掘
Oracle数据误删不用怕，跟我来学日志挖掘
pbeskoyq7ffm4
63 0
pbeskoyq7ffm4
|
4月前
|
SQL Oracle 关系型数据库
Oracle误删数据怎么恢复？
Oracle误删数据怎么恢复？
pbeskoyq7ffm4
51 0
三分钟热度的鱼
|
4月前
|
SQL Oracle Java
实时计算 Flink版产品使用问题之采集Oracle数据时，为什么无法采集到其他TABLESPACE的表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
73 0
三分钟热度的鱼
|
4月前
|
分布式计算 Oracle 关系型数据库
实时计算 Flink版产品使用问题之获取Oracle的数据时无法获取clob类型的数据，该怎么办
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
50 0