列出数据库中子表上没有对应索引的外键

最新推荐文章于 2020-04-22 08:42:15 发布

原创最新推荐文章于 2020-04-22 08:42:15 发布 · 338 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

SQL优化专栏收录该内容

2 篇文章

订阅专栏

本文提供了一段SQL脚本，用于检查Oracle数据库中定义为远程（R类型）的外键约束，并排除了一些预定义的系统所有者。此脚本通过连接多个视图来确保所选的外键约束没有不可用的索引或者位于分区表上且分区状态不可用。

set linesize 150;

col owner for a20;
col COLUMN_NAME for a20;

SELECT c.owner,
         c.constraint_name,
         c.table_name,
         cc.column_name,
         c.status
    FROM dba_constraints c, dba_cons_columns cc
   WHERE c.constraint_type = 'R'
         AND c.owner NOT IN
                ('SYS',
                 'SYSTEM',
                 'SYSMAN',
                 'EXFSYS',
                 'WMSYS',
                 'OLAPSYS',
                 'OUTLN',
                 'DBSNMP',
                 'ORDSYS',
                 'ORDPLUGINS',
                 'MDSYS',
                 'CTXSYS',
                 'AURORA$ORB$UNAUTHENTICATED',
                 'XDB',
                 'FLOWS_030000',
                 'FLOWS_FILES')
         AND c.owner = cc.owner
         AND c.constraint_name = cc.constraint_name
         AND NOT EXISTS
                    (SELECT 'x'
                       FROM dba_ind_columns ic
                      WHERE     cc.owner = ic.table_owner
                            AND cc.table_name = ic.table_name
                            AND cc.column_name = ic.column_name
                            AND cc.position = ic.column_position
                            AND NOT EXISTS
                                       (SELECT owner, index_name
                                          FROM dba_indexes i
                                         WHERE     i.table_owner = c.owner
                                               AND i.index_Name = ic.index_name
                                               AND i.owner = ic.index_owner
                                               AND (i.status = 'UNUSABLE'
                                                    OR i.partitioned = 'YES'
                                                       AND EXISTS
                                                              (SELECT 'x'
                                                                 FROM dba_ind_partitions ip
                                                                WHERE status =
                                                                         'UNUSABLE'
                                                                      AND ip.
                                                                           index_owner =
                                                                             i.
                                                                              owner
                                                                      AND ip.
                                                                           index_Name =
                                                                             i.
                                                                              index_name
                                                               UNION ALL
                                                               SELECT 'x'
                                                                 FROM dba_ind_subpartitions isp
                                                                WHERE status =
                                                                         'UNUSABLE'
                                                                      AND isp.
                                                                           index_owner =
                                                                             i.
                                                                              owner
                                                                      AND isp.
                                                                           index_Name =
                                                                             i.
                                                                              index_name))))
ORDER BY 1, 2
/