Re: Date / Time a table changed

On May 9, 8:37 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> alastair.coo..._at_googlemail.com wrote in news:dfbfde07-fcb3-4250-bbe0-
> cfe258a9a..._at_m36g2000hse.googlegroups.com:
>
> > Hi.
>
> > We are connecting to an Oracle 9i Database, is it possible to query a
> > table to find out the last date / time a row was updated / inserted.
>
> LOGMINER can provide the details.

Ana is correct in that LOGMINER can be used but in my opinion it is impractical for routine use unless you have an idea of when to look or you have the time to review potentially weeks of archived redo logs in your attempt to find the data. But you might need to use Log Miner if it is truely important to find this out on a one time basis but if you have an application where all connections use one Oracle username then the result is probably not going to help unless when the change is made is all you need to know.

Another possibility when an undo tablespace is available and when the change is very recent is you can use flashback query.

But if you need a permanent record or reliable means of checking when a specific row was last updated then storing the data on the row is the only truely effective method available potentially in conjuction with a history (audit) table. With system level auditing the audit data is likely to have to be purge at some point though this data could be archived.

IMHO -- Mark D Powell -- Received on Sat May 10 2008 - 09:00:38 CDT