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

oracle oracle-call-interface
Maximilian picture Maximilian · Nov 5, 2008 · Viewed 240.7k times · Source

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

Paul picture Paul · Jul 26, 2011

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.

Related questions

libaio.so.1: cannot open shared object file

I have a simple test program that when I run I get: ./hello: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory I link it like this: $(CC) $(CCFLAGS) -o hello hello.…

Read More
Fatal error: Call to undefined function oci_connect()

I have the next lines uncommented (in my php.ini): extension=php_oracle.dll extension=php_oci8.dll And I downloaded this file instantclient-basiclite-nt-11.2.0.2.0.zip and unzipped and put that on drive D... I am using Windows XP I set …

Read More
Call to undefined function oci_connect, php_oci8_12c.dll, windows 8.1, php5.6.6

I have a simple php script: <?php $db_user = 'myusername'; $db_pass = 'mypassword'; $db_sid = 'mysid'; $conn = oci_connect( $db_user, $db_pass, $db_sid ); ?> When I run it (from a browser or from the command line), I …

Read More