• Arun Kumar

Last modified table

As a DBA, application team sometimes might ask you to provide details of last modified table in oracle. The table modification can be insert, update or delete. Below queries get details of last or latest modified table in oracle database. Run the queries depending upon the database version.


Last modified table (10g and above)

set linesize 500;
select TABLE_OWNER, TABLE_NAME, INSERTS, UPDATES, DELETES, 
to_char(TIMESTAMP,'YYYY-MON-DD HH24:MI:SS')
from all_tab_modifications
where table_owner<>'SYS' and
EXTRACT(YEAR FROM TO_DATE(TIMESTAMP, 'DD-MON-RR')) > 2010
order by 6;

Last modified table (9i)


In 9i, table monitoring has to be enabled manually or else the all_tab_modifcations wont keep record of changes. 10g onwards, oracle by default records the modifications


Last modified table in oracle for 9i db

col object for a20;
col object_name for a20;
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, 
to_char(LAST_DDL_TIME,'YYYY-MON-DD HH24:MI:SS') 
from dba_objects where LAST_DDL_TIME=(select max(LAST_DDL_TIME) 
from dba_objects WHERE object_type='TABLE');



22 views

Recent Posts

See All

Temp Tablespace utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback