Quick Question: When was the table last modified

Hello All,

In continuation of my previous post,

Quick Question: When is my table last accessed? Does my index is in use?

I have another questions when is my table last modified and what are the ways i can view it.

Here you go,

1) Enabling audit on segment

	1)Enable auditing on table
	2) Use dba_audit_trail to know insert and update statements

2) DBA_TAB_MODIFICATIONS to view the same, statistics_level set to All or Typical
But here are the caevets

  1) If you are in 9i, the data will be pushed from memory to this view 
     every 15 mins
  2) Until 10gr1, the data will be pushed from memory to this view 
     every 3 hours
  3) From 10gr2 onwards this data will be pushed only when you 
     gather statistics on segment 
  4) or manually flush the monitoring info i.e using  
     DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

Background:- Table/segment modifications used to be monitored using monitoring
on /off on segments on 9i, where this was removed and monitoring is
default in 10g and monitors all object modifications in mon_mod$_all

Another important aspect is, for example, on the below lines

	SQL> Select * from dba_tab_modifications where table_name='TEST';

	TABLE_NAME      INSERTS    UPDATES    DELETES trnc TIMESTAMP                    
	------------ ---------- ---------- ---------- ---- -----------------             
	TEST              320          0          0 NO     26-Mar-2012 10:21     

The date is when the record is entered in the table mon_mod$_all not the exact date
when it modified in the original table.

3) If you have awr license, use dba_hist_sql_stat etc.rather relying on statistics to flush or wait to gather

SQL>
select to_char(sn.begin_interval_time,'yy-mm-dd hh24'),
p.object_owner owners, p.object_name Obj_Name, 
p.operation Operation, 
p.options Options, count(1) Idx_Usg_Cnt 
from dba_hist_sql_plan p,dba_hist_sqlstat s ,  
dba_hist_snapshot sn
where p.object_owner = '&USERNAME'
and p.sql_id = s.sql_id and p.object_name='&OBJNAME' 
and p.operation like '%UPDATE%'
and sn.snap_id = t.snap_id 
group by sn.begin_interval_time,p.object_owner,
p.object_name,p.operation,p.options order by 1,2,3

Note:- I am filtering out the operation as update from sqlstat or sqlplan views 
 
 
 TO_CHAR(SN. OWNERS   OBJ_NAME       OPERATION    
 ----------- ------------ ------------------------
 13-02-26 17 TEST     TEST           UPDATE       
 13-02-26 17 TEST     TEST           UPDATE       
 13-02-27 16 TEST     TEST           UPDATE       
 13-02-27 16 TEST     TEST           UPDATE       
 13-03-02 16 TEST     TEST           UPDATE       
 13-03-02 16 TEST     TEST           UPDATE       
 13-03-02 20 TEST     TEST           UPDATE       
 13-03-02 20 TEST     TEST           UPDATE       
 13-03-07 17 TEST     TEST           UPDATE       
 13-03-07 17 TEST     TEST           UPDATE       
 13-03-08 16 TEST     TEST           UPDATE       
 13-03-08 16 TEST     TEST           UPDATE       
 13-03-08 17 TEST     TEST           UPDATE       
 13-03-08 17 TEST     TEST           UPDATE       
 13-03-09 17 TEST     TEST           UPDATE       
 13-03-09 17 TEST     TEST           UPDATE       
 13-03-12 16 TEST     TEST           UPDATE       
 13-03-12 16 TEST     TEST           UPDATE       
 13-03-14 16 TEST     TEST           UPDATE       
 13-03-14 16 TEST     TEST           UPDATE       
 13-03-14 17 TEST     TEST           UPDATE       
 13-03-14 17 TEST     TEST           UPDATE       
 13-03-16 18 TEST     TEST           UPDATE       
 13-03-16 18 TEST     TEST           UPDATE       
 13-03-19 16 TEST     TEST           UPDATE       
 13-03-19 16 TEST     TEST           UPDATE       
 13-03-20 17 TEST     TEST           UPDATE       
 13-03-20 17 TEST     TEST           UPDATE       
 13-03-21 16 TEST     TEST           UPDATE       

Hope this helps