Thursday 19 June 2014

How to know "When was the table last modified" ?

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.

No comments:

Post a Comment