分析user_constraints為何不顯示子表外來鍵列構建的索引歷險記

wisdomone1發表於2015-10-30

背景

   透過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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章