Query to check when the table was updated

[kkkumar]

Hello All,

Is there a query that I can run against a table to see when it was updated last? Updation is either adding a new row or column or updating a row.

Thanks,
Kiran

[davey23uk]

no, not unless you store it yourself

[kkkumar]

FYI:

In Oracle 10gr2, you can do this

SELECT max(SCN_TO_TIMESTAMP(ORA_ROWSCN))
FROM my_table;

I haven't tried that myself.

Thanks,
Kiran

[jmodic]

FYI:

In Oracle 10gr2, you can do this

SELECT max(SCN_TO_TIMESTAMP(ORA_ROWSCN))
FROM my_table;

I haven't tried that myself.

For this to work (i.e. to help the original poster to find out the latest modified row), the table should be created with row-level dependency tracking, that is with ROWDEPENDENCIES parameter specified when the table is *created*. Otherwise, the above query will only help you detect which *block* (not row!) was changed during the last transaction.