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.

Thanks



Alastair@wavetechconsultancy.co.uk

On May 9, 9:45*am, alastair.coo...@googlemail.com wrote:
> 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.
>
> Thanks
>
> Alast...@wavetechconsultancy.co.uk

No, on version 9.2 and lower the only way to know when a row in a
table was changed is to either use a trigger to populate last change
by and when columns on the row or to use the builtin audit command by
access.

Is there a way to get the Date/Time when a row was last updated?
http://www.jlcomp.demon.co.uk/faq/mod_time.html


HTH -- Mark D Powell --

alastair.coombs@googlemail.com wrote in news:dfbfde07-fcb3-4250-bbe0-
cfe258a9aa19@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.

On May 9, 8:37*pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> alastair.coo...@googlemail.com wrote in news:dfbfde07-fcb3-4250-bbe0-
> cfe258a9a...@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 --