分析user_constraints為何不顯示子表外來鍵列構建的索引歷險記
背景
透過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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 基於v$lock.lmode分析父子表外來鍵列是否索引持鎖模式區別之系列六索引模式
- 無索引的外來鍵之主表子表DML操作實驗及結論索引
- 通過在Oracle子表外來鍵上建立索引提高效能Oracle索引
- oracle查詢表資訊（索引，外來鍵，列等）Oracle索引
- MySQL為何不建議使用null列MySqlNull
- 《Activity顯示介面歷險記》—說說View的那些理不清的關係View
- 10.30 索引，外來鍵索引
- 查詢外來鍵約束、子表欄位等資訊的SQLSQL
- 查詢沒有索引的外來鍵索引
- 從零開始構建一個vue專案 --- webpack歷險記VueWeb
- oracle foreign key外來鍵_更新主表對於子表三種行為控制Oracle行為控制
- 外來鍵缺索引檢查指令碼索引指令碼
- 檢查外來鍵是否有索引的指令碼索引指令碼
- 在已存在的表結構上新增主鍵、外來鍵、聯合主鍵、聯合索引的例子索引
- Http歷險記（上）HTTP
- 表外來鍵未加索引之處理索引
- Oracle 外來鍵索引影響阻塞問題Oracle索引
- YYModel 原始碼歷險記：程式碼結構原始碼
- 【fk_index】外來鍵中有無索引的區別Index索引
- SQL SERVER中找出拙劣的約束，索引，外來鍵SQLServer索引
- win10 關機鍵如何不顯示更新_win10關機鍵去掉更新的方法Win10
- js避坑歷險記JS
- Http歷險記(下)-- Struts的秘密HTTP
- 【Analytic】使用分析函式ROW_NUMBER輔助完成外來鍵的索引批量建立函式索引
- 外來鍵有無索引帶來的影響學習與測試索引
- 外來鍵沒有索引哪些DML操作會被阻塞索引
- 一個iOS開發者的Flutter“歷險記”iOSFlutter
- 升級MySQL8.0的歷險記MySql
- MYSQL的外來鍵MySql
- SQL 中使用WMSYS.WM_CONCAT把列轉為行來顯示SQL
- 兩表互為外來鍵的解決方案
- 查詢(看)表的主鍵、外來鍵、唯一性約束和索引索引
- 批量修改欄位長度，考慮主鍵外來鍵索引的情況【轉】索引
- 原因定位：Oracle為何不能使用索引(轉）Oracle索引
- webpack-dashboard直觀顯示webpack構建Web
- 構建基於React18的電子表格程式React
- mysql 外來鍵索引入門介紹，為什麼工作中很少有人使用？MySql索引
- oracle外來鍵索引解決父表鎖定問題Oracle索引