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; |
已知这两个对象分别处于产生死锁的两个语句中,找出他们的主外键关系。