Realistically, if you need this information, you need to store it in the table, use auditing, or do something else to capture changes (i.e. triggers that populate a table of modifications).
max(ORA_ROWSCN) will work to give you the last SCN of a modification (note that by default, this is stored at the block level not at the row level, so rows with the max(ora_rowscn) aren’t necessarily the most recently modified). But Oracle only maintains the mapping of SCN to timestamp for a limited period of time. In the documentation, Oracle guarantees it will maintain the mapping for 120 hours (5 days). If the last modification was more than a few days ago, scn_to_timestamp will no longer work. If your system has a relatively constant rate of SCN generation, you could try to build your own function to generate approximate timestamps but that could produce significant inaccuracies.
dba_tab_modifications is used by the optimizer to identify tables that need new stats gathered so that data is even more transient. If you have statistics gathering enabled every night, you’d expect that information about some tables would get removed every night depending on which tables had fresh statistics gathered. Plus, the timestamp isn’t intended to accurately identify the time the underlying table was modified but the time that Oracle wrote the monitoring information.
If this is something you need going forward, you could
- Add a timestamp to the table that gets populated when a row is modified.
- Add some logging to the stored procedures that lets you identify when tables were modified.
- Put a trigger on the table that logs modifications in whatever form is useful to you.
- Use Oracle’s built-in auditing to capture DML affecting the table.
If you’re really determined, assuming that the database is in archivelog mode and that you have all the archived log files since each table was last modified, you could use LogMiner to read through each archived log and find the timestamp of the last modification. But that will be exceedingly slow and depends on your backup strategy allowing you to recover old log files back to the last change.
CLICK HERE to find out more related problems solutions.