• 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');



181 views

Recent Posts

See All

Generate Table DDL Command

The DBMS_METADATA package allows you to generate DDL commands for any of the database objects inside Oracle database. Generate Table DDL Command Generate View DDL Command Generate Materialized View DD

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