0

Hello DBAs,

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

Regards,
B. Mantri

9
Contributors
10
Replies
22
Views
9 Years
Discussion Span
Last Post by nuruddean
0

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

0

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

0

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

0

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

0

The easiest way to find is

select * from all_tab_modifications

Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

0

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.

-1

This query works:

select dba_objects where object_name='table_name';

Votes + Comments
Wrong answer, atleast write the basic syntax corrent
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.