分析user_constraints为何不显示子表外键列构建的索引历险记

最新推荐文章于 2023-07-13 16:03:41 发布

clg10051

最新推荐文章于 2023-07-13 16:03:41 发布

阅读量391

点赞数

文章标签：数据库 ui 后端

背景

通过select table_name,constraint_name,constraint_type,index_name from user_constraints where table_name in ('T_PARENT','T_CHILD');
查询不到子表外键列构建的索引信息，仅可以查询到父表主键约束自动创建的索引，这到底是为何呢，底层处理机制是什么呢？

结论

1,user_constraints在oracle11g中只会显示主键约束的索引，而不会显示外键约束构建的索引

2,uesr_constraints字典表的底层DDL定义可以通过dba_views获取，记得
在sqlplus查询，先要格式化方可查看结果
set long 99999999
set pagesize 300
select view_name,text from dba_views where view_name='USER_CONSTRAINTS'

3, user_constraints字典的DDL定义如下：
OWNER VIEW_NAME TEXT
---------- ------------------------------------------------------------ --------------------------------------------------------------------------------
SYS USER_CONSTRAINTS select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?'),
o.name, c.condition, ru.name, rc.name,
decode(c.type#, 4,
decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),
NULL),
decode(c.type#, 5, 'ENABLED',
decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),
decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
decode(bitand(c.defer,16),16, 'BAD', null),
decode(bitand(c.defer,32),32, 'RELY', null),
c.mtime,
decode(c.type#, 2, ui.name, 3, ui.name, null),
decode(c.type#, 2, oi.name, 3, oi.name, null),
decode(bitand(c.defer, 256), 256,
decode(c.type#, 4,
case when (bitand(c.defer, 128) = 128
or o.status in (3, 5)
or ro.status in (3, 5)) then 'INVALID'
else null end,
case when (bitand(c.defer, 128) = 128
or o.status in (3, 5)) then 'INVALID'
else null end
),
null),
decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */

4，user_constraints由几个底层表构成，
con$
_base_user
_current_edition_obj
cdef$
obj$
user$

5,我的分析思路为：
A,用上述定义user_constraints的DDL定义语句的SELECT列部分与user_constraints的列部分对应，获取最终DDL定义到底对应哪些基表的列

B,然后仅摘取部分关键列简化DDL定义语句，仅包含约束名，表名，索引，大致如下（目的就是针对性分析，排除无用信息的干扰）

select
ou.name username,
oc.name constraint_name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?') constraint_type,
o.name table_name,
decode(c.type#, 2, oi.name, 3, oi.name, 4,oi.name,null) index_name,
oi.name,
c.type#
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */
and o.name in ('T_PARENT','T_CHILD');

C,接着摘取DDL定义语句的SELECT部分，其中代码为 decode(c.type#, 2, oi.name, 3, oi.name, null)
对应user_constraints的index_name

以及在上述的简化版ddlwhere条件添加o.name in代码，这个对应
select table_name,constraint_name,constraint_type,index_name from user_constraints where table_name in ('T_PARENT','T_CHILD')
的WHERE条件，我就想找对应DDL的基表的哪个基表及列

D,基于C部分分解，即说白了，显示索引就是由
cdef$
obj$这2个表
以及
cdef$表的列type#
和obj$表的name

以及_CURRENT_EDITION_OBJ表

E, 现在你拿到了这些信息，如何继续分析呢，大思路就是要从这些基表在WHERE条件中的关联然后最终要导入到SELECT部分
decode(c.type#, 2, oi.name, 3, oi.name, null)

我采用的方法就是反推，以and o.name in ('T_PARENT','T_CHILD')为基础，
然后在DDL WHERE条件中，找到_CURRENT_EDITION_OBJ表的关联表
是cdef$,且找到关联列即obj#
然后通过cdef$表找到关联表obj$,
最后就是一条线，从WHERE入门，和select部分的代码 decode(c.type#, 2, oi.name, 3, oi.name, null)关联起来了

F，根据上述的分析思路，发现是 cdef$通过enabled和obj$.obj#关联，而cdef$.enabled通过查看sql.bsq定义，发现其列含义是控制约束是否启用的，不知为何ORACLE会在此列存储主键索引的信息

SQL> select con#,obj#,type#,enabled from cdef$ where obj# in (74752);

CON# OBJ# TYPE# ENABLED
---------- -------- ---------- ----------
11923 74752 4 1

G,基于上述思路，我更新了字典表cdef$匹配idx_t_child子表外键索引的enabled值，由原来的1值（表示约束启用）变更为对应idx_t_child外键索引的索引对象标识号（可由dba_objects获取）

H,在测试中，感受到ORACLE底层字典设计的复杂及精妙，还有很多不解之处

J，本文最大价值，在于我掌握了分析复杂底层DDL定义SQL的技术，哈哈，开心

K，另外：在ORACLE11G中，sql.bsq不再存储原来底层字典的DDL定义了，此文件仅是一个引用指针文件，真正存储底层字典DDL定义在$ORACLE_HOME/RDBMS/admin/dcore.sql

l, 还有一种感觉，在分析比较复杂的问题时，可能要多尝试几种方法，像我这次测试，就是换了几种思路，最后才成功的，这也是理解一个事物必经之路
相信自己，不要轻易放弃，加油

P，测试过程有些乱，大家关注其思路即可，欢迎交流

测试

创建父表
SQL> create table t_parent(a int,b int,c int);

Table created.

创建子表
SQL> create table t_child(a int,b int);

Table created.

创建主键约束
SQL> alter table t_parent add constraint pk_t_parent primary key(a);

Table altered.

创建子表外键约束列的索引
SQL> create index idx_t_child on t_child(a);

Index created.

直接在索引字典表可以查询出索引信息
SQL> select table_name,column_name,index_name from user_ind_columns where table_name='T_CHILD';

TABLE_NAME COLUMN_NAME INDEX_NAME
------------------------------ ------------------------------ --------------------
T_CHILD A IDX_T_CHILD

但从约束字典中却查不出来外键列构建索引的信息
SQL> select table_name,constraint_name,constraint_type,index_name from user_constraints where table_name in ('T_PARENT','T_CHILD');

TABLE_NAME CONSTRAINT_NAME CO INDEX_NAME
--------------- -------------------- -- ------------------------------------------------------------
T_CHILD FK_T_CHILD R
T_PARENT PK_T_PARENT P PK_T_PARENT

SQL> select table_name,constraint_name,constraint_type,index_name from dba_constraints where table_name in ('T_PARENT','T_CHILD');

TABLE_NAME CONSTRAINT_NAME CO INDEX_NAME
-------------------- -------------------- -- --------------------
T_CHILD FK_T_CHILD R
T_PARENT PK_T_PARENT P PK_T_PARENT

会不会是底层字典表把外键列索引的信息给过滤了呢，先要找到这个存储索引的底层字典表，在其基础上看执行计划主要看FILTER，因为它是负责条件过滤的

从trace来看，相关的底层字典表obj$,con$,user$,cdef$,从字典来看，索引信息是存储在obj$这个底层表中，所以只要看如下与此表相关的FILTER顾虑对应执行计划即可
SQL> set autot traceonly
SQL> select table_name,constraint_name,constraint_type,index_name from dba_constraints where table_name in ('T_PARENT','T_CHILD');

Execution Plan
----------------------------------------------------------
Plan hash value: 4010684654

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1908 | 283 (1)| 00:00:04 |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN OUTER | | 12 | 1908 | 283 (1)| 00:00:04 |
| 3 | NESTED LOOPS OUTER | | 12 | 1860 | 282 (1)| 00:00:04 |
|* 4 | HASH JOIN OUTER | | 12 | 1464 | 258 (1)| 00:00:04 |
| 5 | NESTED LOOPS OUTER | | 12 | 1416 | 256 (1)| 00:00:04 |
|* 6 | HASH JOIN | | 12 | 1320 | 254 (1)| 00:00:04 |
| 7 | NESTED LOOPS | | | | | |
| 8 | NESTED LOOPS | | 12 | 1272 | 253 (1)| 00:00:04 |
| 9 | NESTED LOOPS OUTER | | 12 | 984 | 241 (1)| 00:00:03 |
| 10 | NESTED LOOPS | | 12 | 960 | 205 (1)| 00:00:03 |
|* 11 | HASH JOIN | | 3 | 177 | 204 (1)| 00:00:03 |
|* 12 | INDEX FAST FULL SCAN | I_OBJ2 | 3 | 111 | 202 (0)| 00:00:03 |
| 13 | INDEX FULL SCAN | I_USER2 | 88 | 1936 | 1 (0)| 00:00:01 |
|* 14 | TABLE ACCESS CLUSTER | CDEF$ | 4 | 84 | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_COBJ# | 1 | | 0 (0)| 00:00:01 |
| 16 | VIEW PUSHED PREDICATE | _CURRENT_EDITION_OBJ | 1 | 2 | 3 (0)| 00:00:01 |
|* 17 | FILTER | | | | | |
| 18 | NESTED LOOPS | | 1 | 34 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | I_OBJ1 | 1 | 12 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | I_USER2 | 1 | 22 | 1 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 22 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 0 (0)| 00:00:01 |
| 25 | TABLE ACCESS BY INDEX ROWID| CON$ | 1 | 24 | 1 (0)| 00:00:01 |
| 26 | INDEX FULL SCAN | I_USER2 | 88 | 352 | 1 (0)| 00:00:01 |
| 27 | TABLE ACCESS BY INDEX ROWID | CON$ | 1 | 8 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | I_CON2 | 1 | | 0 (0)| 00:00:01 |
| 29 | INDEX FULL SCAN | I_USER2 | 88 | 352 | 1 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 33 | 2 (0)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | I_OBJ1 | 1 | | 1 (0)| 00:00:01 |
| 32 | INDEX FULL SCAN | I_USER2 | 88 | 352 | 1 (0)| 00:00:01 |
| 33 | NESTED LOOPS | | 1 | 29 | 2 (0)| 00:00:01 |
|* 34 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 35 | INDEX RANGE SCAN | I_OBJ4 | 1 | 9 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
"O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR
"O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR
"O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM
SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND
"O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_e
dition_id')))))
2 - access("OI"."OWNER#"="UI"."USER#"(+))
4 - access("RC"."OWNER#"="U"."USER#"(+))
6 - access("OC"."OWNER#"="U"."USER#")
11 - access("O"."OWNER#"="U"."USER#")
12 - filter("O"."NAME"='T_CHILD' OR "O"."NAME"='T_PARENT')
14 - filter("C"."TYPE#"<>12 AND "C"."TYPE#"<>8 AND ("C"."TYPE#"<14 OR "C"."TYPE#">17))
15 - access("C"."OBJ#"="O"."OBJ#")
17 - filter("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND
"O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR
BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR
"O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR
"O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87) AND
(SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND
"U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR EXISTS (SELECT 0 FROM
SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#" AND "O2"."TYPE#"=88 AND
"O2"."DATAOBJ#"=:B1 AND "U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_e
dition_id')))))
19 - access("O"."OBJ#"="C"."ROBJ#")
20 - access("O"."OWNER#"="U"."USER#")
22 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
)))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
)))
23 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
24 - access("OC"."CON#"="C"."CON#")
28 - access("C"."RCON#"="RC"."CON#"(+))
31 - access("C"."ENABLED"="OI"."OBJ#"(+))
34 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
)))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'
)))
35 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
782 consistent gets
0 physical reads
0 redo size
859 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

SQL>

即使索引存储在obj$，我们先来分析下obj$底层表的含义及构成

SQL> select object_id,object_name from dba_objects where object_name='IDX_T_CHILD';

OBJECT_ID OBJECT_NAME
---------- ------------------------------
74773 IDX_T_CHILD

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status from obj$ where obj#=74773;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS
---------- ---------- ---------- -------------------- ---------- ---------- ----------
74773 74773 84 IDX_T_CHILD 4 1 1

看到这里你想到什么，肯定是obj$中某些列的值进行了过滤，所以就不会显示idx_t_child的索引
SQL> select obj#,dataobj#,owner#,name,namespace,type#,status,spare1,spare2,spare3 from obj$ where obj#=74773;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS SPARE1 SPARE2 SPARE3
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
74773 74773 84 IDX_T_CHILD 4 1 1 6 65535 84

SQL> select obj#,spare4,spare5,spare6,flags from obj$ where obj#=74773;

OBJ# SPARE4 SPARE5 SPARE6 FLAGS
---------- ---------- -------------------- -------------------------
74773 0

SQL>

为了验证我这个思路，我们再查看下PK_T_PARENT主键约束对应索引的信息,经过对比我们发现spare1的值不同，主键索引对应的是0，而外键索引对应的是6

SQL> select object_id,object_name from dba_objects where object_name='PK_T_PARENT';

OBJECT_ID OBJECT_NAME
---------- ------------------------------
74757 PK_T_PARENT

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status from obj$ where obj#=74757;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS
---------- ---------- ---------- -------------------- ---------- ---------- ----------
74757 74757 84 PK_T_PARENT 4 1 1

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status,spare1,spare2,spare3 from obj$ where obj#=74757;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS SPARE1 SPARE2 SPARE3
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
74757 74757 84 PK_T_PARENT 4 1 1 0 65535 84

SQL> select obj#,spare4,spare5,spare6,flags from obj$ where obj#=74757;

OBJ# SPARE4 SPARE5 SPARE6 FLAGS
---------- ---------- -------------------- ------------ ----------
74757 0

现在有个很简单的方法，进行证明，看不是因为这个obj$的spare1值不同，所以导致外键索引显示不出来

---更新obj$前先备份obj$表

---其实也不用全备份obj$,仅备份更新的记录即可
SQL> create table obj$_bak as select * from obj$;

Table created.

---更新obj$表对应idx_t_child的spare1为0
SQL> update obj$ set spare1=0 where obj#=74773;

1 row updated.

SQL> commit;

Commit complete.

---查询user_constraints看可否查询出外键索引
---还是没有显示出来，可见不止这一个条件列，还有其它列的因系在起作用
SQL> select table_name,constraint_name,constraint_type,index_name from dba_constraints where table_name in ('T_PARENT','T_CHILD');

TABLE_NAME CONSTRAINT_NAME CO INDEX_NAME
-------------------- -------------------- -- --------------------
T_CHILD FK_T_CHILD R
T_PARENT PK_T_PARENT P PK_T_PARENT

再看下user$,这个只有1条匹配记录，所以根本和这个表无关
SQL> set linesize 300
SQL> select user#,name,type#,spare1,spare2,spare3,spare4,spare5,spare6 from user$ where user#=84;

USER# NAME TYPE# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------
84 SCOTT 1 0 S:69A82601
8FDEA9155E
F85BD1E9CD
2DD0EDDAD6
D99DAAD814
9E48726802
8D

再看下con$,可见与此表关系也不大，记录下，con$与user$通过user#联系起来
SQL> select owner#,name,con#,spare1,spare2,spare3,spare4,spare5,spare6 from con$ where owner#=84;

OWNER# NAME CON# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------
84 FK_DEPTNO 10796 0
84 FK_T_CHILD 11923 0
84 PK_DEPT 10794 0
84 PK_EMP 10795 0
84 PK_T_PARENT 11914 0

再看下cdef$
可见con#即约束标识号，通过con#与con$联系起来,同时我们发现父表t_parent在此表有信息，而子表t_child在此表无记录，会不会因为此表的原因呢
也就是没有写入子表外键索约束信息到此表中
SQL> set linesize 300
SQL> select con#,obj#,cols,type#,spare1,spare2,spare3,spare4,spare5,spare6 from cdef$ where con# in (11914,11923);

CON# OBJ# COLS TYPE# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11914 74751 1 2 6 0 45780028
11923 74752 1 4 6 0 45813014
可见type#代表约束类型，cols代表约束包含几个列,spare2,spare3代表创建对象的SCN BASE及SCN WRAP

到这儿我们就要了解下cdef$的含义了？
[oracle@seconary admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@seconary admin]$ ll -l *bsq*
-rw-r--r-- 1 oracle oinstall 25905 Mar 19 2009 daw.bsq
-rw-r--r-- 1 oracle oinstall 90238 May 1 2009 dcore.bsq
-rw-r--r-- 1 oracle oinstall 2832 Oct 23 2006 ddm.bsq
-rw-r--r-- 1 oracle oinstall 674 Jul 14 2008 ddst.bsq
-rw-r--r-- 1 oracle oinstall 17800 Feb 28 2009 denv.bsq
-rw-r--r-- 1 oracle oinstall 1364 Oct 31 2005 dexttab.bsq
-rw-r--r-- 1 oracle oinstall 4937 Oct 31 2005 dfmap.bsq
-rw-r--r-- 1 oracle oinstall 728 Oct 31 2005 djava.bsq
-rw-r--r-- 1 oracle oinstall 24260 Apr 6 2009 dlmnr.bsq
-rw-r--r-- 1 oracle oinstall 9570 Apr 24 2007 dmanage.bsq
-rw-r--r-- 1 oracle oinstall 25509 Jun 8 2007 dobj.bsq
-rw-r--r-- 1 oracle oinstall 34077 Mar 31 2009 doptim.bsq
-rw-r--r-- 1 oracle oinstall 46200 Apr 2 2008 dpart.bsq
-rw-r--r-- 1 oracle oinstall 16679 Jan 8 2007 dplsql.bsq
-rw-r--r-- 1 oracle oinstall 17673 Oct 31 2005 drac.bsq
-rw-r--r-- 1 oracle oinstall 113665 Mar 26 2009 drep.bsq
-rw-r--r-- 1 oracle oinstall 133258 Jul 25 2009 dsec.bsq
-rw-r--r-- 1 oracle oinstall 17751 Mar 9 2009 dsqlddl.bsq
-rw-r--r-- 1 oracle oinstall 19958 Jul 30 2008 dsummgt.bsq
-rw-r--r-- 1 oracle oinstall 13506 Apr 24 2006 dtools.bsq
-rw-r--r-- 1 oracle oinstall 5474 Oct 31 2006 dtxnspc.bsq
-rw-r--r-- 1 oracle oinstall 3473 Aug 14 2009 prvtbsqu.plb
-rw-r--r-- 1 oracle oinstall 2446570 Aug 14 2009 recover.bsq
-rw-r--r-- 1 oracle oinstall 53130 Jul 14 2008 sql.bsq

[oracle@seconary admin]$ more dcore.bsq

REM NOTE
REM Logminer/Streams uses contents of this table.
REM Please do not reuse any flags without verifying the impact of your
REM changes on inter-op.
create table cdef$ /* constraint definition table */
( con# number not null, /* constraint number */
obj# number not null, /* object number of base table/view */
cols number, /* number of columns in constraint */
type# number not null, /* constraint type: */
/* Note: If new types are added then please ensure that the */
/* {....}_CONSTRAINTS family of views reflect the new type. */
/* 1 = table check, 2 = primary key, 3 = unique, */
/* 4 = referential, 5 = view with CHECK OPTION, */
/* 6 = view READ ONLY check */
/* 7 - table check constraint associated with column NOT NULL */
/* 8 - hash expressions for hash clusters */
/* 9 - Scoped REF column constraint */
/* 10 - REF column WITH ROWID constraint */
/* 11 - REF/ADT column with NOT NULL const */
/* 12 - Log Groups for supplemental logging */
/* 13 - Allow PKref vals Storage in REF col */
/* 14 - Primary key supplemental logging */
/* 15 - Unique key supplemental logging */
/* 16 - Foreign key supplemental logging */
/* 17 - All column supplemental logging */
robj# number, /* object number of referenced table */
rcon# number, /* constraint number of referenced columns */
rrules varchar2(3), /* future: use this columns for pendant */
match# number, /* referential constraint match type: */
/* null = FULL, 1 = PARTIAL */
/* this column can also store information for other constraint types */
refact number, /* referential action: */
/* null = RESTRICT, 1 = CASCADE, 2 = SET NULL, 3 = SET DEFAULT */
enabled number, /* is constraint enabled? NULL if disabled */
condlength number, /* table check condition text length */
condition long, /* table check condition text */
intcols number, /* number of internal columns in constraint */
mtime date, /* date this constraint was last enabled-disabled */
defer number, /* 0x01 constraint is deferrable */
/* 0x02 constraint is deferred */
/* 0x04 constraint has been system validated */
/* 0x08 constraint name is system generated */
/* 0x10 constraint is BAD, depends on current century */
/* 0x20, optimizer should RELY on this constraint */
/* 0x40 Log Group ALWAYS option */
/* 0x80 (view related) constraint is invalid */
/* 0x100 constraint depends on a view */
/* 0x200 constraint is a partitioning constraint */
spare1 number, /* sql version flag: see kpul.h */
spare2 number, /* create/last modify constraint SCN wrap */
spare3 number, /* create/last modify constraint SCN base */
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date
)

所以我感觉还是在OBJ$上面有区别,发现spare2列又不同了
SQL> select obj#,dataobj#,owner#,name,namespace,type#,status from obj$ where obj# in (74751,74752);

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS
---------- ---------- ---------- -------------------- ---------- ---------- ----------
74751 74751 84 T_PARENT 1 2 1
74752 74752 84 T_CHILD 1 2 1

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status,spare1,spare2,spare3 from obj$ where obj# in (74751,74752);

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS SPARE1 SPARE2 SPARE3
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
74751 74751 84 T_PARENT 1 2 1 6 2 84
74752 74752 84 T_CHILD 1 2 1 6 1 84

SQL> select obj#,spare4,spare5,spare6,flags from obj$ where obj# in (74751,74752);

OBJ# SPARE4 SPARE5 SPARE6 FLAGS
---------- ---------- ---------- ---------- ----------
74751 0
74752 0

SQL> select obj#,name,subname,ctime,mtime,stime,remoteowner,linkname,oid$ from obj$ where obj# in (74751,74752);

OBJ# NAME SUBNAME CTIME MTIME STIME REMOTEOWNER LINKNAME OID$
-------- --------------- ---------- ------------------- ------------------- ------------------- --------------- -------------------- --------------------------------
74751 T_PARENT 2015-10-28 22:07:15 2015-10-28 23:05:21 2015-10-28 22:07:15
74752 T_CHILD 2015-10-28 22:07:29 2015-10-29 12:42:04 2015-10-28 22:07:29

对比下2个索引的基表数据差异
SQL> select object_id,object_name from dba_objects where object_name in ('PK_T_PARENT','IDX_T_CHILD');

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
74773 IDX_T_CHILD
74757 PK_T_PARENT

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status from obj$ where obj# in (74773,74757);

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS
-------- ---------- ---------- --------------- ---------- ---------- ----------
74757 74757 84 PK_T_PARENT 4 1 1
74773 74773 84 IDX_T_CHILD 4 1 1

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status,spare1,spare2,spare3 from obj$ where obj# in (74773,74757);

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS SPARE1 SPARE2 SPARE3
-------- ---------- ---------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
74757 74757 84 PK_T_PARENT 4 1 1 0 65535 84
74773 74773 84 IDX_T_CHILD 4 1 1 0 65535 84

SQL> select obj#,spare4,spare5,spare6,flags from obj$ where obj# in (74773,74757);

OBJ# SPARE4 SPARE5 SPARE6 FLAGS
-------- ---------- ---------- ---------- ----------
74757 0
74773 0

好像从上述仍然没有发现数据的差异，再来看看user_constraints字典DDL定义

SQL> select owner,object_name,object_type from dba_objects where object_name='USER_CONSTRAINTS';

OWNER OBJECT_NAME OBJECT_TYPE
------------------------------------------------------------ -------------------------------------------------- --------------------------------------
SYS USER_CONSTRAINTS VIEW
PUBLIC USER_CONSTRAINTS SYNONYM

SQL> set pagesize 100
SQL> set long 999999999
SQL> select owner,view_name,text from dba_views where view_name='USER_CONSTRAINTS';

OWNER VIEW_NAME TEXT
---------- ------------------------------------------------------------ --------------------------------------------------------------------------------
SYS USER_CONSTRAINTS select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?'),
o.name, c.condition, ru.name, rc.name,
decode(c.type#, 4,
decode(c.refact, 1, 'CASCADE', 2, 'SET NULL', 'NO ACTION'),
NULL),
decode(c.type#, 5, 'ENABLED',
decode(c.enabled, NULL, 'DISABLED', 'ENABLED')),
decode(bitand(c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
decode(bitand(c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
decode(bitand(c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
decode(bitand(c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
decode(bitand(c.defer,16),16, 'BAD', null),
decode(bitand(c.defer,32),32, 'RELY', null),
c.mtime,
decode(c.type#, 2, ui.name, 3, ui.name, null),
decode(c.type#, 2, oi.name, 3, oi.name, null),
decode(bitand(c.defer, 256), 256,
decode(c.type#, 4,
case when (bitand(c.defer, 128) = 128
or o.status in (3, 5)
or ro.status in (3, 5)) then 'INVALID'
else null end,
case when (bitand(c.defer, 128) = 128
or o.status in (3, 5)) then 'INVALID'
else null end
),
null),
decode(bitand(c.defer, 256), 256, 'DEPEND ON VIEW', null)
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */

SQL>

对应下user_consrtraints与其DDL底层定义基表的对应列关系，可见其constraint_name对应con$表的name列,而table_name列对应sys."_CURRENT_EDITION_OBJ"表的name
SQL> desc user_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)

SQL> desc "_CURRENT_EDITION_OBJ";
Name Null? Type
----------------- -------- ------------
OBJ# NOT NULL NUMBER
DATAOBJ# NUMBER
DEFINING_OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
NAMESPACE NOT NULL NUMBER
SUBNAME VARCHAR2(30)
TYPE# NOT NULL NUMBER
CTIME NOT NULL DATE
MTIME NOT NULL DATE
STIME NOT NULL DATE
STATUS NOT NULL NUMBER
REMOTEOWNER VARCHAR2(30)
LINKNAME VARCHAR2(128
)
FLAGS NUMBER
OID$ RAW(16)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(100
0)
SPARE5 VARCHAR2(100
0)
SPARE6 DATE
OWNER# NUMBER
DEFINING_EDITION VARCHAR2(30)

针对性的简化了user_constraints的DDL定义，仅选取了相关的一些列
select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?') ctype,
o.name,
decode(c.type#, 2, oi.name, 3, oi.name, null) index_name
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */
and o.name in ('T_PARENT','T_CHILD');

NAME NAME CT NAME INDEX_NAME
--------------- --------------- -- --------------- ------------------------------
SCOTT FK_T_CHILD R T_CHILD
SCOTT PK_T_PARENT P T_PARENT PK_T_PARENT

--移除FILTER谓词，进行对比测试,可见不是FILTER谓词决定的
select ou.name, oc.name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?') ctype,
o.name,
decode(c.type#, 2, oi.name, 3, oi.name, null) index_name
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
--and c.type# != 8
--and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
--and (c.type# != 12) /* don't include log group cons */
and o.name in ('T_PARENT','T_CHILD');

NAME NAME CT NAME INDEX_NAME
--------------- --------------- -- --------------- ------------------------------
SCOTT FK_T_CHILD R T_CHILD
SCOTT PK_T_PARENT P T_PARENT PK_T_PARENT

我们继续分析，decode(c.type#, 2, oi.name, 3, oi.name, null) index_name ，这是index_name获取的DDL定义部分，可见
与sys.cdef$,
sys.obj$,
也就是说基于表t_child,sys.cdef$.type其列值不等于2或者3

摘录下上述关于sys.cdef$的测试

可见con#即约束标识号，通过con#与con$联系起来,同时我们发现父表t_parent在此表有信息，而子表t_child在此表无记录，会不会因为此表的原因呢
也就是没有写入子表外键索约束信息到此表中
SQL> set linesize 300
SQL> select con#,obj#,cols,type#,spare1,spare2,spare3,spare4,spare5,spare6 from cdef$ where con# in (11914,11923);

CON# OBJ# COLS TYPE# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11914 74751 1 2 6 0 45780028
11923 74752 1 4 6 0 45813014
可见type#代表约束类型，cols代表约束包含几个列,spare2,spare3代表创建对象的SCN BASE及SCN WRAP

可见cdef$中的列obj#为表对象标识号
SQL> select object_id,object_name from dba_objects where object_id in (74751,74752);

OBJECT_ID OBJECT_NAME
---------- ------------------------------
74751 T_PARENT
74752 T_CHILD

从cdef$的type#值确实有差异
SQL> select con#,obj#,cols,type#,spare1,spare2,spare3,spare4,spare5,spare6 from cdef$ where con# in (11914,11923);

CON# OBJ# COLS TYPE# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11914 74751 1 2 6 0 45780028
11923 74752 1 4 6 0 45813014

我们显式更新下cdef$中的type#，让user_constraints显示外键索引的索引信息

这样思路就有了哈，我们直接重写ddl定义语句

---重写此列代码，让type=4也显示出来

--虽然显示出来，但显示的外键索引的名称不对，显示了一个_next_object
select ou.name username,
oc.name constraint_name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?') constraint_type,
o.name table_name,
decode(c.type#, 2, oi.name, 3, oi.name, 4,oi.name,null) index_name,
oi.name,
c.type#
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */
and o.name in ('T_PARENT','T_CHILD');

USERNAME CONSTRAINT_NAME CO TABLE_NAME INDEX_NAME
--------------- -------------------- -- --------------- ------------------------------
SCOTT FK_T_CHILD R T_CHILD _NEXT_OBJECT
SCOTT PK_T_PARENT P T_PARENT PK_T_PARENT

SQL> select count(*) from obj$ where name='_NEXT_OBJECT';

COUNT(*)
----------
1

SQL> select count(*) from obj$ where name='IDX_T_CHILD';

COUNT(*)
----------
1

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status from obj$ where name in ('_NEXT_OBJECT','IDX_T_CHILD');

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS
-------- ---------- ---------- --------------- ---------- ---------- ----------
1 74779 0 _NEXT_OBJECT 1 0 0
74773 74773 84 IDX_T_CHILD 4 1 1

SQL> select obj#,dataobj#,owner#,name,namespace,type#,status,spare1,spare2,spare3 from obj$ where name in ('_NEXT_OBJECT','IDX_T_CHILD');

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS SPARE1 SPARE2 SPARE3
-------- ---------- ---------- --------------- ---------- ---------- ---------- ---------- ---------- ----------
1 74779 0 _NEXT_OBJECT 1 0 0 0 65535 0
74773 74773 84 IDX_T_CHILD 4 1 1 0 65535 84

突然想到在测试前部分有个更新底层的动作，需要恢复这个动作

SQL> update obj$ set spare1=6 where obj#=74773;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

还是老样子没变化
USERNAME CONSTRAINT_NAME CO TABLE_NAME INDEX_NAME NAME TYPE#
--------------- -------------------- -- --------------- ------------------------------ -------------------- ------
SCOTT FK_T_CHILD R T_CHILD _NEXT_OBJECT _NEXT_OBJECT 4
SCOTT PK_T_PARENT P T_PARENT PK_T_PARENT PK_T_PARENT 2

可见cdef$.obj#即索引的对象标识号
SQL> select object_id,object_name from dba_objects where object_id in (74773) or object_name='PK_T_PARENT';

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
74773 IDX_T_CHILD
74757 PK_T_PARENT

再次分析DDL定义，主要关注
select ou.name username,
oc.name constraint_name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?') constraint_type,
o.name table_name,
decode(c.type#, 2, oi.name, 3, oi.name, 4,oi.name,null) index_name,
oi.name,
c.type#
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+) ---可见cdef$.enable与obj$.obj#关联起来，并且是左关键，
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */
and o.name in ('T_PARENT','T_CHILD');

基于上述思路，我们看看对应表的列值
SQL> desc cdef$;
Name Null? Type
----------------- -------- ------------
CON# NOT NULL NUMBER
OBJ# NOT NULL NUMBER
COLS NUMBER
TYPE# NOT NULL NUMBER
ROBJ# NUMBER
RCON# NUMBER
RRULES VARCHAR2(3)
MATCH# NUMBER
REFACT NUMBER
ENABLED NUMBER
CONDLENGTH NUMBER
CONDITION LONG
INTCOLS NUMBER
MTIME DATE
DEFER NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(100
0)
SPARE5 VARCHAR2(100
0)
SPARE6 DATE

由于表关联比较复杂，我们从源头开始梳理，然后反推回去
SQL> select obj#,name from "_CURRENT_EDITION_OBJ" where name in ('T_PARENT','T_CHILD');

OBJ# NAME
-------- ---------------
74752 T_CHILD --关注此条记录
74751 T_PARENT

然后这个表又与cdef$关联起来

先查看cdef$的列enabled的含义，表示约束是否启用
enabled number, /* is constraint enabled? NULL if disabled */

所以最终的修正版本是：---可见cdef$.enable与obj$.obj#关联起来，并且是左关键，所以我感觉应该是c.obj# = oi.obj#(+)

SQL> select con#,obj#,type#,enabled from cdef$ where obj# in (74751,74752);

CON# OBJ# TYPE# ENABLED
---------- -------- ---------- ----------
11914 74751 2 74757 --这里有些难以理解，此列是存储约束是否启用的啊，为何存储了主键索引的对象标识号呢
11923 74752 4 1 ---这是我们要关注的记录

最后这个表双与obj$进行关联,所以最终才会显示_next_object
SQL> select obj#,dataobj#,owner#,name,namespace,type#,status from obj$ where obj#=1;

OBJ# DATAOBJ# OWNER# NAME NAMESPACE TYPE# STATUS
-------- ---------- ---------- --------------- ---------- ---------- ----------
1 74859 0 _NEXT_OBJECT 1 0 0

所以只要把cdef$对应记录的enabled列值改为外键索引的对象标识号，即可
SQL> select object_id,object_name from dba_objects where object_id in (74773)

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
74773 IDX_T_CHILD

更新前记录
SQL> select con#,obj#,type#,enabled from cdef$ where obj# in (74752);

CON# OBJ# TYPE# ENABLED
---------- -------- ---------- ----------
11923 74752 4 1

更新
SQL> update cdef$ set enabled=74773 where obj#=74752;

1 row updated.

SQL> commit;

Commit complete.

终于成功了，太开心，历经辛苦啊，颇多波折
select ou.name username,
oc.name constraint_name,
decode(c.type#, 1, 'C', 2, 'P', 3, 'U',
4, 'R', 5, 'V', 6, 'O', 7,'C', 8, 'H', 9, 'F',
10, 'F', 11, 'F', 13, 'F', '?') constraint_type,
o.name table_name,
decode(c.type#, 2, oi.name, 3, oi.name, 4,oi.name,null) index_name,
oi.name,
c.type#
from sys.con$ oc, sys.con$ rc, sys."_BASE_USER" ou, sys."_BASE_USER" ru,
sys."_CURRENT_EDITION_OBJ" ro, sys."_CURRENT_EDITION_OBJ" o, sys.cdef$ c,
sys.obj$ oi, sys.user$ ui
where oc.owner# = ou.user#
and oc.con# = c.con#
and c.obj# = o.obj#
and c.rcon# = rc.con#(+)
and c.enabled = oi.obj#(+)
and oi.owner# = ui.user#(+)
and rc.owner# = ru.user#(+)
and c.robj# = ro.obj#(+)
and o.owner# = userenv('SCHEMAID')
and c.type# != 8
and (c.type# < 14 or c.type# > 17) /* don't include supplog cons */
and (c.type# != 12) /* don't include log group cons */
and o.name in ('T_PARENT','T_CHILD');

USERNAME CONSTRAINT_NAME CO TABLE_NAME INDEX_NAME NAME TYPE#
--------------- -------------------- -- --------------- ------------------------------ -------------------- ------
SCOTT FK_T_CHILD R T_CHILD IDX_T_CHILD IDX_T_CHILD 4
SCOTT PK_T_PARENT P T_PARENT PK_T_PARENT PK_T_PARENT 2

个人简介：

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院

河北廊坊新奥集团公司

项目经验:

中国电信3G项目AAA系统数据库部署及优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

联系方式：

手机：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1818728/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1818728/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
分析user_constraints为何不显示子表外键列构建的索引历险记

背景通过select table_name,constraint_name,constraint_type,index_name from user_constraints where table_name ...
复制链接

扫一扫