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')
/

© 2011, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.

相关文章 | Related posts:

1. Script:收集数据库安全风险评估信息
2. Script:列出数据库中子表上没有对应索引的外键
3. Script:Generate A DDL Script For A Table
4. Script:List Buffer Cache Details
5. Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
6. Script:List OBJECT DEPENDENT
7. Script:partition table into rowid extent chunks
8. Oracle Voyager Worm 一段匿名块SQL蠕虫
9. Script:when transaction will finish rollback