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

Query

select tab.owner as table_schema,
       tab.table_name,
       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',
     'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
     'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM','TSMSYS','WK_TEST',
     'WKSYS','WKPROXY','WMSYS','XDB','APEX_040000','APEX_PUBLIC_USER','DIP',
     'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
     'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
      and obj.last_ddl_time > (current_date - INTERVAL '60' DAY)
order by last_modify desc;

Columns

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

Rows

• 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