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.

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

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