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