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

来源：互联网发布：网络礼仪资料编辑：程序博客网时间：2024/06/16 03:23
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/
阅读全文
0 0