last modified date of a table

[bmantri]

Hello DBAs,

Can any one guide me how can i get the last modified date(insert/update/delete) of a table?

Regards,
B. Mantri

[pilpip]

Hello DBAs,

Can any one guide me how can i get the last modified date(insert/update/delete) of a table?

Regards,
B. Mantri

try all_tab_modifications or user_tab_modifcations

[bmantri]

Hello pilpil,
Thanks for reply.
but I am not getting any records when selecting from all_tab_modifications or user_tab_modifications.
I tried connecting to both sys and system user. In system user I have created one table TEST and inserted some rows.

SQL> select * from all_tab_modifications where table_name = 'TEST';

no rows selected
Please respond soon!

Thanks,
BMantri

[Nige Ridd]

If the table has been modified recently and is 10g, you can try

select scn_to_timestamp(max(ora_rowscn))
from test;

Think I've seen things say it's only up to 5 days you can use this.

If you need to and can modify the database, you could always create your own version of the user_tab_modifications by putting an INSERT/UPDATE/DELETE trigger on the tables you want to track and create a small table which holds the same sort of data as the user_tab_modifications does.

Nige

[bmantri]

Hello Nige,

Thanks a lot for your valuable suggestion. It is working with some of my tables but for others it is giving the following error.
SQL> select scn_to_timestamp(max(ora_rowscn)) from SLB_FILE_ATTACHMENTS
2 ;
select scn_to_timestamp(max(ora_rowscn)) from SLB_FILE_ATTACHMENTS
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
Do you have any idea about this error and how can I find the last modified date for this kind of tables?
Please help me to fix this issue.

Many many Thanks,
BMantri

[Mukeshkoshym]

The easiest way to find is

select * from all_tab_modifications

[arturozz]

try:

select to_char(scn_to_timestamp(max(ora_rowscn))) from table_name

[amadensor]

Hello Nige,

ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
Do you have any idea about this error and how can I find the last modified date for this kind of tables?
Please help me to fix this issue.

This only works as long as the snapshot is still around. After that, it appears to go into a frozen state. All off my records older than a certain point have the same SCN.

[Khadhar]

This query works:

select dba_objects where object_name='table_name';

[tmadan]

Hi, you can detect the date of the last update on the table either by using the following:

select * from all_tab_modifications where table_name like TABLE_NAME

select scn_to_timestamp(max(ora_rowscn)) from TABLE_NAME

[nuruddean]

how I want to know what, who and when the update happen?