Query below lists:

(A) tables having foreign keys, along with the number of foreign keys and number of tables they refer to. Tables accessible to the current user in Oracle database

(B) tables having foreign keys, along with the number of foreign keys and number of tables they refer to. All tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select cons.owner as schema_name,
       cons.table_name,
       count(*) as foreign_keys,
       count(distinct ref_cons.table_name) as referenced_tables
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'
-- excluding some Oracle maintained schemas
and cons.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') 
group by cons.owner,
         cons.table_name
order by count(*) desc, cons.owner, cons.table_name;

B. If you have privilege on dba_constraints

select cons.owner as schema_name,
       cons.table_name,
       count(*) as foreign_keys,
       count(distinct ref_cons.table_name) as referenced_tables
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'
-- excluding some Oracle maintained schemas
and cons.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')
group by cons.owner,
         cons.table_name
order by count(*) desc, cons.owner, cons.table_name;

Columns

• schema_name - schema name, owner of the table
• table_name - name of the table
• foreign_keys - number of foreign keys in a table
• referenced_tables - number of tables referred to in foreign keys

Rows

• One row represents one table having foreign keys in a database
• Scope of rows: (A) tables having foreign keys, along with the number of foreign keys and number of tables they refer to. Tables accessible to the current user in Oracle database, (B) tables having foreign keys, along with the number of foreign keys and number of tables they refer to. All tables in Oracle database
• Ordered by number of foreign keys in a table (descending), schema name, table name

Sample results