Find recently created tables in Oracle database

Ania - Dataedo Team Ania 2018-12-07

Table of Contents:


    Query below lists:

    (A) all tables accessible to the current user in Oracle database that were created within the last 60 days

    (B) all tables in Oracle database that were created within the last 60 days

    Query was executed under the Oracle9i Database version.

    Query

    A. Tables accessible to the current user

    select owner as  schema_name,
           object_name as table_name,
           created
    from sys.all_objects
    where object_type = 'TABLE'
    -- excluding some Oracle maintained schemas
    and 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 created > sysdate - 60
    order by created desc,
             owner,
             object_name;
    

    B. If you have privilege on dba_objects

    select owner as  schema_name,
           object_name as table_name,
           created
    from sys.dba_objects
    where object_type = 'TABLE'
    -- excluding some Oracle maintained schemas
    and 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 created > sysdate - 60
    order by created desc,
             owner,
             object_name;
    

    Columns

    • schema_name - table owner, schema name
    • table_name - table name
    • created - table's creation date

    Rows

    • One row represents one table in a database
    • Scope of rows: (A) all tables accessible to the current user in Oracle database that were created within the last 60 days, (B) all tables in Oracle database that were created within the last 60 days
    • Ordered by table's creation date (descending), schema name, table name

    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