This query listed tables that have no foreign keys, meaning they are not referencing any table or are not on the "many" side of FK.

Query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something we called Loner Tables. This diagram illustrates the concept:

Learn more about Loner Tables

Query below lists:

(A) all tables accessible to the current user in Oracle database that don't have foreign keys and are not referenced by other tables with foreign keys

(B) all tables in Oracle database that don't have foreign keys and are not referenced by other tables with foreign keys

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select 'No FKs >-' refs,
       t.owner as schema_name,
       t.table_name,
       '>- no FKs' fks
from sys.all_tables t 
left join (select distinct owner as schema_name,
                           table_name
           from sys.all_constraints 
           where constraint_type = 'R'
           ) fks
           on t.owner = fks.schema_name
           and t.table_name = fks.table_name
left join (select distinct cons.r_owner as schema_name,
                           ref_cons.table_name as table_name
           from sys.all_constraints cons
           inner join sys.all_constraints ref_cons 
                      on cons.r_owner = ref_cons.owner
                      and cons.r_constraint_name = ref_cons.constraint_name
           where cons.constraint_type = 'R'
           ) referenced_by_others          
           on t.owner = referenced_by_others.schema_name
           and t.table_name = referenced_by_others.table_name
where 
fks.table_name is null
and referenced_by_others.table_name is null
-- excluding some Oracle maintained schemas
and t.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', 'WKSYS',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by t.owner,
         t.table_name;

B. If you have privilege on dba_tables and dba_constraints

select 'No FKs >-' refs,
       t.owner as schema_name,
       t.table_name,
       '>- no FKs' fks
from sys.dba_tables t 
left join (select distinct owner as schema_name,
                           table_name
           from sys.dba_constraints 
           where constraint_type = 'R'
           ) fks
           on t.owner = fks.schema_name
           and t.table_name = fks.table_name
left join (select distinct cons.r_owner as schema_name,
                           ref_cons.table_name as table_name
           from sys.dba_constraints cons
           inner join sys.dba_constraints ref_cons 
                      on cons.r_owner = ref_cons.owner
                      and cons.r_constraint_name = ref_cons.constraint_name
           where cons.constraint_type = 'R'
           ) referenced_by_others          
           on t.owner = referenced_by_others.schema_name
           and t.table_name = referenced_by_others.table_name
where 
fks.table_name is null
and referenced_by_others.table_name is null
-- excluding some Oracle maintained schemas
and t.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', 'WKSYS',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')
order by t.owner,
         t.table_name;

Columns

• refs - symbol indicating lack of references by foreign key constraints
• schema_name - table owner, schema name
• table_name - table name
• fks - symbol indicating lack of foreign key constraints

Rows

• One row represents one table that doesn't have foreign keys and is not referenced by other tables with foreign keys
• Scope of rows: tables that are not related (not referencing and not being referenced with foreign key constraints) with any table: (A) all tables accessible to the current user in Oracle database, (B) all tables in Oracle database
• Ordered by schema name, table name

Sample results