Find recently created tables in Oracle database

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.