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 /
列出数据库中子表上没有对应索引的外键
最新推荐文章于 2023-07-28 14:50:38 发布