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

发布时间:2014-10-22 13:59:24编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"列出数据库中子表上没有对应索引的外键",主要涉及到列出数据库中子表上没有对应索引的外键方面的内容,对于列出数据库中子表上没有对应索引的外键感兴趣的同学可以参考一下。

from http://www.askmaclean.com/archives/list-foreign-keys-with-no-matching-index-on-child-table-causes-locks.html REM List foreign keys with no matching index on child table - causes locks 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


上一篇:c++ ado连接fatal error C1083: 无法打开类型库文件:“msado15.dll”
下一篇:Silverlight中导出Excel文件

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款