查找数据库中主外键关系表

sys@ORCL> desc dba_constraints
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                          VARCHAR2(30)
 CONSTRAINT_NAME                                       NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                                VARCHAR2(1)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                               LONG
 R_OWNER                                                        VARCHAR2(30)
 R_CONSTRAINT_NAME                                              VARCHAR2(30)
 DELETE_RULE                                                    VARCHAR2(9)
 STATUS                                                         VARCHAR2(8)
 DEFERRABLE                                                     VARCHAR2(14)
 DEFERRED                                                       VARCHAR2(9)
 VALIDATED                                                      VARCHAR2(13)
 GENERATED                                                      VARCHAR2(14)
 BAD                                                            VARCHAR2(3)
 RELY                                                           VARCHAR2(4)
 LAST_CHANGE                                                    DATE
 INDEX_OWNER                                                    VARCHAR2(30)
 INDEX_NAME                                                     VARCHAR2(30)
 INVALID                                                        VARCHAR2(7)
 VIEW_RELATED                                                   VARCHAR2(14)

描述约束的详细情况

 

sys@ORCL> desc dba_cons_columns
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 OWNER                                                 NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                                       NOT NULL VARCHAR2(30)
 TABLE_NAME                                            NOT NULL VARCHAR2(30)
 COLUMN_NAME                                                    VARCHAR2(4000)
 POSITION                                                       NUMBER

 

描述哪些列上有哪些约束。

select con1.table_name "外键表",
       c1.column_name  "外键列",
       con2.table_name "主键表",
       c2.column_name  "主键列",
       con1.r_constraint_name,
       con2.constraint_name       
  from dba_constraints  con1,
       dba_constraints  con2,
       dba_cons_columns c1,
       dba_cons_columns c2
 where con1.owner = 'HR'
   and con1.constraint_type = 'R'
   and con1.constraint_name = c1.constraint_name
   and con1.r_constraint_name = con2.constraint_name
   and con2.constraint_name = c2.constraint_name;

找出主外键表关系。

 

select distinct con1.table_name "外键表",         c1.column_name  "外键列",         con2.table_name "主键表",         c2.column_name  "主键列",         con1.r_constraint_name,         con2.constraint_name           from dba_constraints  con1,         dba_constraints  con2,         dba_cons_columns c1,         dba_cons_columns c2   where con1.owner = 'NBPM'     and ( con1.table_name in ('BPM_USER','BPM_USER_ROLEGROUP') OR con2.table_name in ('BPM_USER','BPM_USER_ROLEGROUP'))    and con1.constraint_type = 'R'     and con1.constraint_name = c1.constraint_name     and con1.r_constraint_name = con2.constraint_name     and con2.constraint_name = c2.constraint_name;

 

已知这两个对象分别处于产生死锁的两个语句中，找出他们的主外键关系。