找出哪些table没有primary key

可以依照SCHEMA展出没有设PK的TABLE

def schema_name = &&owner_name

col sname format a30 heading 'Schema'

col tname format a30 heading 'Table Name'

select

dt.owner sname,

dt.table_name tname

from

sys.dba_tables dt

where

dt.owner like upper('&schema_name') escape '\'

and

not exists

(select 'x' from sys.dba_constraints dc

where dc.owner = dt.owner

and dc.table_name = dt.table_name

and dc.constraint_type = 'P')

order by 1,2

/

执行结果

Schema Table Name

------------------------------ ------------------------------

APPLE TEST_TB

APPLE KKK

APPLE P1

APPLE PLAN_TABLE

APPLE QQ

APPLE T1

APPLE T2

APPLE T3