How to find out when an Oracle table was updated the last time

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

http://blog.tanelpoder.com/2009/02/07/when-was-a-table-last-changed/