哪个 Oracle 视图包含所有约束？

Question

我试图从 user_objects 表中获取约束，如下所示：

 select CASE object_type
      WHEN 'DATABASE LINK' then 'dblinks'
      WHEN 'FUNCTION' then 'functions'
      WHEN 'INDEX' then 'indexes'
      WHEN 'PACKAGE' then 'packages'
      WHEN 'PROCEDURE' then 'procedures'
      WHEN 'SEQUENCE' then 'sequences'
      WHEN 'TABLE' then 'tables'
      WHEN 'TRIGGER' then 'triggers'
      WHEN 'VIEW' then 'views'
      WHEN 'SYNONYM' then 'synonyms'
      WHEN 'GRANT' then 'grants'
      WHEN 'CONSTRAINT' then 'constraints'
      ELSE object_type
      END||'|'||
      CASE object_type
      WHEN 'DATABASE LINK' then 'DB_LINK'
      ELSE object_type
      END||'|'||object_name
from user_objects
where object_name not like 'BIN$%'
and object_type not like '%PARTITION'
and object_type not in ('PACKAGE BODY')
order by object_type
; 

select distinct object_type
from user_objects
; 

但是...... USER_OBJECTS 只有这些类型 FUNCTION
INDEX、PACKAGE、PACKAGE BODY、PROCEDURE、SEQUENCE、TABLE、TRIGGER、VIEW，因为从 user_objects 中选择不同的 object_type；返回他们。所以这个查询根本没有给我任何约束。

有没有办法从 Oracle 获取所有约束？我应该使用哪个 Oracle 视图？

Answer 1

   select * from user_constraints

Answer 2

约束不是对象。所以他们处于不同的视图，即USER_CONSTRAINTS。对于外部约束，您需要自连接：

select * from user_constraints c
left join user_constraints r on r.owner = c.r_owner and r.constraint_name = c.r_constraint_name
where c.constraint_type = 'R';

一些详细信息也可以在 USER_CONS_COLUMNS 中找到。