Script:列出没有主键或唯一索引的表

以下脚本可以用于列出数据库中没有主键的表,已排除了系统schema:
REM List tables with no primary key

SELECT owner, table_name
  FROM dba_tables
 WHERE 1 = 1
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
MINUS
SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_type = 'P'
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
/
以下脚本可以用于列出数据库中没有唯一约束或索引的表,已排除了系统schema:
REM List tables with no unique key or index

SELECT owner, table_name
  FROM dba_all_tables
 WHERE 1 = 1
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
MINUS
SELECT owner, table_name
  FROM dba_constraints
 WHERE constraint_type = 'U'
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
MINUS
SELECT owner, table_name
  FROM dba_indexes
 WHERE uniqueness = 'UNIQUE'
       AND owner NOT IN
              ('SYS',
               'SYSTEM',
               'SYSMAN',
               'EXFSYS',
               'WMSYS',
               'OLAPSYS',
               'OUTLN',
               'DBSNMP',
               'ORDSYS',
               'ORDPLUGINS',
               'MDSYS',
               'CTXSYS',
               'AURORA$ORB$UNAUTHENTICATED',
               'XDB',
               'FLOWS_030000',
               'FLOWS_FILES')
/

本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1277813


上一篇:Git:错误:error:src refspec master does not match any


下一篇:log4j 配置