5
select ora_rowscn from table_name;

ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn is useful for determining approximately when a row was last updated.

How do I get the timestamp from here? Also, is there any query by which I can get all the last modified tables in a particular schema?

Akash Chaudhuri
  • 63
  • 2
  • 9

2 Answers2

2

SCN_TO_TIMESTAMP takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN. 

SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) 
FROM employees
WHERE employee_id = 188;

If you have 10g or above, you can use Oracle's flashback functionality to get this information. You would need to enable flashback;

select  table_name ,max(commit_timestamp) 
from FLASHBACK_TRANSACTION_QUERY 
where table_owner = 'YOUR_SCHEMA' 
      and operation in ('INSERT','UPDATE','DELETE','MERGE') 
group by table_name
XING
  • 9,608
  • 4
  • 22
  • 38
2

There should be no need to get involved with SCNs for what you are asking. Why not just:

begin dbms_stats.flush_database_monitoring_info; end;

select * from dba_tab_modifications
where timestamp >= sysdate - 7
order by timestamp desc;

Unless you are (foolishly?) running with only BASIC statistics level (default is TYPICAL, which is higher), this should work fine in any 11g database or later.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
  • Thanks Matthew, could have been specific and a little filtered and sorted. SELECT Table_Owner, Table_Name, Timestamp, Inserts, Updates, Deletes FROM Dba_Tab_Modifications WHERE Timestamp >= Sysdate - 15 AND Upper(Table_Owner) IN ( 'SCHEMA1', 'SCHEMA2', 'SCHEMA3' ) ORDER BY 1, 3 DESC; – khalidmehmoodawan Feb 07 '22 at 11:09