Tables in database often have standard columns, such as creation_date, created_by, modification_date, modified_by.

Query below lists:

(A) all tables accessible to the current user in Oracle database that don't have column with a specific name

(B) all tables in Oracle database that don't have column with a specific name

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select tables.owner as schema_name,
       tables.table_name
from sys.all_tables tables
left join (select owner as schema_name,
                  table_name
           from sys.all_tab_columns 
           where column_name = 'CREATION_DATE'
           ) tables_with_col 
                      on tables.owner = tables_with_col.schema_name
                        and tables.table_name = tables_with_col.table_name
where tables_with_col.table_name is null
-- excluding some Oracle maintained schemas
and tables.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',
   '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', 'WKSYS')  
order by tables.owner, 
         tables.table_name;

B. If you have privilege on dba_tab_columns and dba_tables

select tables.owner as schema_name,
       tables.table_name
from sys.dba_tables tables
left join (select owner as schema_name,
                  table_name
           from sys.dba_tab_columns 
           where column_name = 'CREATION_DATE'
           ) tables_with_col 
                      on tables.owner = tables_with_col.schema_name
                        and tables.table_name = tables_with_col.table_name
where tables_with_col.table_name is null
-- excluding some Oracle maintained schemas
and tables.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',
   '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', 'WKSYS')  
order by tables.owner, 
         tables.table_name;

Columns

• schema_name - table owner, schema name
• table_name - table name

Rows

• One row represents one table that don't have column with a specific name
• Scope of rows: (A) all tables accessible to the current user in Oracle database that don't have column with a specific name, (B) all tables in Oracle database that don't have column with a specific name
• Ordered by schema name, table name

Sample results