CODEWITHSUNDEEP
oracle-databasetimestamplast-modifiedsql-timestamp
Akash Chaudhuri
Akash Chaudhuri

Reputation: 71

How do I get a list of all the last modified tables in an Oracle database?

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?

Upvotes: 5

Views: 12626

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17944

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.

Upvotes: 2

XING
XING

Reputation: 9886

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

Upvotes: 2

Related Questions