如何找出上次更新 Oracle 表的时间 [英] How to find out when an Oracle table was updated the last time

问题描述

我能否找出最后一次对 Oracle 数据库中的表执行 INSERT、UPDATE 或 DELETE 语句的时间，如果是，如何知道?

Can I find out when the last INSERT, UPDATE or DELETE statement was performed on a table in an Oracle database and if so, how?

一点背景:Oracle 版本是 10g.我有一个定期运行的批处理应用程序，从单个 Oracle 表读取数据并将其写入文件.如果自上次作业运行以来数据没有改变，我想跳过这个.

A little background: The Oracle version is 10g. I have a batch application that runs regularly, reads data from a single Oracle table and writes it into a file. I would like to skip this if the data hasn't changed since the last time the job ran.

该应用程序是用 C++ 编写的，并通过 OCI 与 Oracle 通信.它以普通"用户身份登录 Oracle，因此我无法使用任何特殊的管理工具.

The application is written in C++ and communicates with Oracle via OCI. It logs into Oracle with a "normal" user, so I can't use any special admin stuff.

好的，特殊管理人员"并不是一个很好的描述.我的意思是:除了从表中选择和调用存储过程之外，我不能做任何事情.如果想在 2010 年之前完成它，那么更改数据库本身的任何内容(如添加触发器)很遗憾不是一个选项.

Okay, "Special Admin Stuff" wasn't exactly a good description. What I mean is: I can't do anything besides SELECTing from tables and calling stored procedures. Changing anything about the database itself (like adding triggers), is sadly not an option if want to get it done before 2010.

推荐答案

由于您使用的是 10g，您可能会使用 ORA_ROWSCN 伪列.这为您提供了导致行更改的最后一个 SCN(系统更改编号)的上限.由于这是一个递增序列，您可以存储您所看到的最大 ORA_ROWSCN，然后仅查找 SCN 大于该值的数据.

Since you are on 10g, you could potentially use the ORA_ROWSCN pseudocolumn. That gives you an upper bound of the last SCN (system change number) that caused a change in the row. Since this is an increasing sequence, you could store off the maximum ORA_ROWSCN that you've seen and then look only for data with an SCN greater than that.

默认情况下，ORA_ROWSCN 实际上是在块级别维护的，因此对块中任何行的更改都会更改块中所有行的 ORA_ROWSCN.如果我们谈论的是正常"数据访问模式，而目的是尽量减少多次处理的行数，而无需更改，那么这可能就足够了.您可以使用 ROWDEPENDENCIES 重建表，这将导致在行级别跟踪 ORA_ROWSCN，这为您提供更详细的信息，但需要一次性的努力来重建表.

By default, ORA_ROWSCN is actually maintained at the block level, so a change to any row in a block will change the ORA_ROWSCN for all rows in the block. This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we're talking about "normal" data access patterns. You can rebuild the table with ROWDEPENDENCIES which will cause the ORA_ROWSCN to be tracked at the row level, which gives you more granular information but requires a one-time effort to rebuild the table.

另一种选择是配置更改数据捕获 (CDC) 之类的内容，并使您的 OCI 应用程序订阅表更改，但这也需要一次性配置 CDC.

Another option would be to configure something like Change Data Capture (CDC) and to make your OCI application a subscriber to changes to the table, but that also requires a one-time effort to configure CDC.

这篇关于如何找出上次更新 Oracle 表的时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！