如何查明 Oracle 表上次更新时间

发布于 2024-07-08 13:55:17 字数 393 浏览 9 评论 0原文

我能否查出对 Oracle 数据库中的表执行最后一条 INSERT、UPDATE 或 DELETE 语句的时间?如果是,如何查明?

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

该应用程序是用 C++ 编写的,并通过 OCI 与 Oracle 进行通信。 它使用“普通”用户登录 Oracle,因此我无法使用任何特殊的管理内容。

编辑:好的,“特殊管理人员”并不是一个很好的描述。 我的意思是:除了从表中进行选择和调用存储过程之外,我什么也做不了。 遗憾的是,如果想在 2010 年之前完成,更改数据库本身的任何内容(例如添加触发器)都不是一个选择。

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?

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.

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.

Edit: 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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(11

豆芽 2024-07-15 13:55:17

我参加这个聚会确实迟到了,但我是这样做的:

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from myTable;

它已经足够接近我的目的了。

I'm really late to this party but here's how I did it:

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn)) from myTable;

It's close enough for my purposes.

清醇 2024-07-15 13:55:17

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

默认情况下,ORA_ROWSCN 实际上是在块级别维护的,因此对块中任何行的更改都会更改该块中所有行的 ORA_ROWSCN。 如果我们讨论的是“正常”数据访问模式,如果目的是在不进行任何更改的情况下最大程度地减少多次处理的行数,这可能就足够了。 您可以使用 ROWDEPENDENCIES 重建表,这将导致在行级别跟踪 ORA_ROWSCN,从而为您提供更精细的信息,但需要一次性重建表桌子。

另一种选择是配置诸如更改数据捕获 (CDC) 之类的功能,并使 OCI 应用程序成为表更改的订阅者,但这也需要一次性配置 CDC。

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.

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.

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.

山有枢 2024-07-15 13:55:17
SELECT * FROM all_tab_modifications;
SELECT * FROM all_tab_modifications;
空城旧梦 2024-07-15 13:55:17

向您的 DBA 询问有关审计的信息。 他可以使用一个简单的命令开始审计,例如:

AUDIT INSERT ON user.table

然后您可以查询表 USER_AUDIT_OBJECT 以确定自上次导出以来您的表上是否有插入。

谷歌Oracle审计了解更多信息...

Ask your DBA about auditing. He can start an audit with a simple command like :

AUDIT INSERT ON user.table

Then you can query the table USER_AUDIT_OBJECT to determine if there has been an insert on your table since the last export.

google for Oracle auditing for more info...

梨涡 2024-07-15 13:55:17

您可以对结果运行某种校验和并将其存储在本地吗? 然后,当您的应用程序查询数据库时,您可以比较其校验和并确定是否应该导入它?

您似乎可以使用 ORA_HASH 函数来完成此任务。

更新:另一个好资源:10g的ORA_HASH函数判断两个Oracle表数据是否相等

Could you run a checksum of some sort on the result and store that locally? Then when your application queries the database, you can compare its checksum and determine if you should import it?

It looks like you may be able to use the ORA_HASH function to accomplish this.

Update: Another good resource: 10g’s ORA_HASH function to determine if two Oracle tables’ data are equal

秋风の叶未落 2024-07-15 13:55:17

Oracle 可以监视表的更改,并且当发生更改时可以在 PL/SQL 或 OCI 中执行回调函数。 回调获取一个对象,该对象是已更改的表的集合,并且具有已更改的 rowid 的集合以及操作类型(Ins、upd、del)。

所以你甚至不用走到餐桌旁,而是坐下来等待叫号。 只有当有需要修改的地方你才会去。

它称为数据库更改通知。 正如贾斯汀提到的,它比 CDC 简单得多,但两者都需要一些花哨的管理内容。 好的部分是,这些都不需要更改应用程序。

需要注意的是,CDC 适合大容量表,而 DCN 则不然。

Oracle can watch tables for changes and when a change occurs can execute a callback function in PL/SQL or OCI. The callback gets an object that's a collection of tables which changed, and that has a collection of rowid which changed, and the type of action, Ins, upd, del.

So you don't even go to the table, you sit and wait to be called. You'll only go if there are changes to write.

It's called Database Change Notification. It's much simpler than CDC as Justin mentioned, but both require some fancy admin stuff. The good part is that neither of these require changes to the APPLICATION.

The caveat is that CDC is fine for high volume tables, DCN is not.

笑叹一世浮沉 2024-07-15 13:55:17

如果服务器上启用了审计,只需使用

SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_NAME IN ()

If the auditing is enabled on the server, just simply use

SELECT *
FROM ALL_TAB_MODIFICATIONS
WHERE TABLE_NAME IN ()
雨的味道风的声音 2024-07-15 13:55:17

您需要在插入、更新、删除上添加一个触发器,将另一个表中的值设置为 sysdate。

当您运行应用程序时,它会读取该值并将其保存在某处,以便下次运行时它有一个可以比较的引用。

您会考虑“特殊管理人员”吗?

最好描述一下您实际在做什么,这样您才能得到更清晰的答案。

You would need to add a trigger on insert, update, delete that sets a value in another table to sysdate.

When you run application, it would read the value and save it somewhere so that the next time it is run it has a reference to compare.

Would you consider that "Special Admin Stuff"?

It would be better to describe what you're actually doing so you get clearer answers.

东风软 2024-07-15 13:55:17

批处理写入文件需要多长时间? 最简单的方法可能是让它继续运行,然后将文件与上次运行的文件副本进行比较,看看它们是否相同。

How long does the batch process take to write the file? It may be easiest to let it go ahead and then compare the file against a copy of the file from the previous run to see if they are identical.

云柯 2024-07-15 13:55:17

如果有人仍在寻找答案,他们可以使用 Oracle Oracle 10g 附带的数据库更改通知 功能。 它需要CHANGE NOTIFICATION系统权限。 您可以注册侦听器何时触发返回应用程序的通知。

If any one is still looking for an answer they can use Oracle Database Change Notification feature coming with Oracle 10g. It requires CHANGE NOTIFICATION system privilege. You can register listeners when to trigger a notification back to the application.

软糖 2024-07-15 13:55:17

请使用以下语句

select * from all_objects ao where ao.OBJECT_TYPE = 'TABLE'  and ao.OWNER = 'YOUR_SCHEMA_NAME'

Please use the below statement

select * from all_objects ao where ao.OBJECT_TYPE = 'TABLE'  and ao.OWNER = 'YOUR_SCHEMA_NAME'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文