在特定时间后更新所有表的行 [英] Get all table's row updated after a specific time

问题描述

背景: 我有一个oracle表，该表在创建表时没有任何特定的列作为时间戳.该表可以包含数百万行.

Background: I have an oracle table, the table doesn't have any specific column as timestamp while table creation script. This table can have millions of rows.

示例:

Employee {Emp_No, Name, Manager,Division, Role, Region}

我的追求:如果通过该表上的作业进行了任何更新，我可以知道哪些行已更新. oracle是否为我可以利用的每一行提供任何内部时间戳.我可以在查询中使用它来获取所有记录.

My quest: If any updates happened through a job on that table, can i know which all rows got updated. Does oracle have any internal timestamp for each row which i can leverage. Can i use it in query to get all records.

原因:我需要向我的团队展示那些模棱两可的记录并未因我们所有人都怀疑的工作而得到更新.

Reason: I need to show my team those ambiguous records weren't updated by the job we all are suspecting.

推荐答案

Oracle具有ORA_ROWSCN伪列.该列返回在行或数据块上进行的最后一个事务的保守的上限系统更改号(SCN)".对于上一次更改块或行的时间，这是一个很好的估计.

Oracle has ORA_ROWSCN Pseudocolumn. This columns returns "the conservative upper bound system change number (SCN)" of last transaction made on row or data block. This is a good estimate for when the block or row was last changed.

如果使用ROWDEPENDENCIES创建表，则ORA_ROWSCN返回scn作为行. NOROWDEPENDENCIES是默认值，在这种情况下，Oracle在块级别跟踪SCN.

If your table is create with ROWDEPENDENCIES, ORA_ROWSCN returns scn for row. NOROWDEPENDENCIES is the default, in which case Oracle tracks SCN at the block level.

SCN_TO_TIMESTAMP允许您将scn转换为时间戳，但是对于旧的scn则会引发异常.

SCN_TO_TIMESTAMP allows you to converto scn to timestamp but for old scn it raises exception.

这篇关于在特定时间后更新所有表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！