Find recently modified tables in Oracle database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-23

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept