Find recently modified tables in Oracle database

The query below lists all tables that was modified in the last 60 days by any DDL statement.


select tab.owner as table_schema,
       obj.last_ddl_time as last_modify
from all_tables tab
join all_objects obj on tab.owner = obj.owner
     and tab.table_name = obj.object_name
where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS',
      and obj.last_ddl_time > (current_date - INTERVAL '60' DAY)
order by last_modify desc;


  • table_schema - schema name
  • table_name - table name
  • last_modify - last table modify time (by any DDL statement)


  • One row: represents one table
  • Scope of rows: all tables which was last modified in the last 60 days in all schemas
  • Ordered by: modify time descending

Sample results

There are no comments. Click here to write the first comment.