这样怎么样:

SELECT c.constraint_name, c.constraint_type, c2.constraint_name, c2.constraint_type, c2.table_name
  FROM dba_constraints c JOIN dba_constraints c2 ON (c.r_constraint_name = c2.constraint_name)
 WHERE c.table_name = <TABLE_OF_INTEREST>
   AND c.constraint_TYPE = 'R';

不。在Oracle SQL Developer中没有这样的选项。

您必须手动执行查询或使用其他工具(例如PLSQL Developer有这样的选项)。下面的SQL是PLSQL开发者使用的SQL:

select table_name, constraint_name, status, owner
from all_constraints
where r_owner = :r_owner
and constraint_type = 'R'
and r_constraint_name in
 (
   select constraint_name from all_constraints
   where constraint_type in ('P', 'U')
   and table_name = :r_table_name
   and owner = :r_owner
 )
order by table_name, constraint_name

其中r_owner是模式，r_table_name是要查找引用的表。名称区分大小写

要小心，因为在Oracle SQL Developer的reports选项卡上有一个选项“All tables / Dependencies”，这个选项来自ALL_DEPENDENCIES，它指的是“当前用户可访问的过程、包、函数、包体和触发器之间的依赖关系，包括对没有任何数据库链接创建的视图的依赖关系”。那么，这份报告对你的问题毫无价值。

你可以从ALL_CONSTRAINTS视图中查询:

SELECT table_name
FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R' -- "Referential integrity"
  AND r_constraint_name IN
    ( SELECT constraint_name
      FROM ALL_CONSTRAINTS
      WHERE table_name = 'EMP'
        AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key"
    );

为sql developer插件添加上面的答案，使用下面的xml将有助于获得与外键相关的列。

    <items>
        <item type="editor" node="TableNode" vertical="true">
        <title><![CDATA[FK References]]></title>
        <query>
            <sql>
                <![CDATA[select a.owner,
                                a.constraint_name,
                                a.table_name,
                                b.column_name,
                                a.status
                         from   all_constraints a
                         join   all_cons_columns b ON b.constraint_name = a.constraint_name
                         where  a.constraint_type = 'R'
                                and exists(
                                   select 1
                                   from   all_constraints
                                   where  constraint_name=a.r_constraint_name
                                          and constraint_type in ('P', 'U')
                                          and table_name = :OBJECT_NAME
                                          and owner = :OBJECT_OWNER)
                                   order by table_name, constraint_name]]>
            </sql>
        </query>
        </item>
    </items>

要将其作为扩展添加到SQL Developer，请执行以下操作:

将下面的代码保存到一个xml文件(例如fk_ref.xml):

<items>
    <item type="editor" node="TableNode" vertical="true">
    <title><![CDATA[FK References]]></title>
    <query>
        <sql>
            <![CDATA[select a.owner,
                            a.table_name,
                            a.constraint_name,
                            a.status
                     from   all_constraints a
                     where  a.constraint_type = 'R'
                            and exists(
                               select 1
                               from   all_constraints
                               where  constraint_name=a.r_constraint_name
                                      and constraint_type in ('P', 'U')
                                      and table_name = :OBJECT_NAME
                                      and owner = :OBJECT_OWNER)
                               order by table_name, constraint_name]]>
        </sql>
    </query>
    </item>
</items>

将扩展添加到SQL Developer: 工具>首选项 数据库>用户定义扩展 点击“添加行”按钮 在类型中选择“编辑器”，位置是你上面保存xml文件的位置 单击“确定”，然后重新启动SQL Developer 导航到任何表，您现在应该在SQL表旁边看到一个附加选项卡，标记为FK References，其中显示新的FK信息。 参考 http://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47sql-086233.html

仅用主表名替换table_name

select *
from all_constraints
where r_constraint_name in (
select constraint_name
from all_constraints
where table_name='table_name'
);