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

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

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

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