Re: Timestamp of Last update on a TABLE.

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 16 Jan 2007 15:27:11 -0800
Message-ID: <1168990026.439577@bubbleator.drizzle.com>

joel garry wrote:
> chandu wrote:

>> Hi All,
>>
>> Is there any way/command of getting the TIME STAMP OF LAST UPDATE MADE
>> on ORACLE TABLE ?
>>

>
> There are a couple of things that could be done, but nothing by
> default. It depends exactly what you want to do with the information
> as to which way you should use.
>
> As far as the database is concerned, updates occur based on system
> change numbers, which aren't necessarily tightly correllated to the
> time.

I'm not sure this is true.

If tables are created with ROWDEPENDENCIES then I believe they are.

It should also be possible to perform a VERSIONS BETWEEN type query to determine the answer ... a variation on the following:

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, program_id, line_number, customer_id, order_date
FROM fb_test
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE program_id = '787';

One need only query for versions_operation = 'U' and look for the max SCN.

But no I have not tested it and can't right now so whether this works is open to a real-world test.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Jan 16 2007 - 17:27:11 CST