[20140131]toad看constraints的問題.txt

[20140131]toad看constraints的問題.txt

今天使用toad檢視constraints(在schema browser模式)發現一個奇怪的情況，發現約束的型別顯示？。
感覺有點奇怪。

我使用toad版本是 11.6.0.43,使用其他版本看也一樣。

使用toad只帶的跟蹤程式SQL Tracker發現，執行如下：

SELECT       CN.NAME constraint_name, decode(c.type#, 1, 'Check', 2, 'Primary Key', 3, 'Unique Key',
                    4, 'Referential Integrity', 5, 'Check Option on a View', 6, 'Read Only Option on a View', 7,'Check', '?') constraint_type,
             ru.name R_OWNER, rc.name R_CONSTRAINT_NAME,
             decode(c.type#, 5, 'Enabled',
                    decode(c.enabled, NULL, 'Disabled', 'Enabled')) status,
             decode(c.type#, 4,
                    decode(c.refact, 1, 'Cascade', 2, 'Set Null', 'No Action'),
                    NULL) delete_rule, c.condition search_condition
            ,decode(bitand(c.defer, 1), 1, 'Deferrable', 'Not Deferrable') deferrable
            ,decode(bitand(c.defer, 2), 2, 'Deferred', 'Immediate') deferred
            ,decode(bitand(c.defer, 4), 4, 'Validated', 'Not Validated') validated
            ,decode(bitand(c.defer, 8), 8, 'Generated Name', 'User Name') generated
            ,decode(bitand(c.defer,16),16, 'Bad', null) bad
            ,decode(bitand(c.defer,32),32, 'Rely', null) rely
      FROM   SYS.CDEF$ C, SYS.CON$ CN, SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U,
             SYS.CON$ RC, SYS.USER$ RU, SYS."_CURRENT_EDITION_OBJ" RO
      WHERE  C.CON# = CN.CON#
      AND    C.OBJ# = O.OBJ#
      AND    O.OWNER# = U.USER#
      AND    C.RCON# = RC.CON#(+)
      AND    RC.OWNER# = RU.USER#(+)
      AND    C.ROBJ# = RO.OBJ#(+)
      AND    U.NAME = 'SCOTT'
      AND    O.NAME = 'T'
      AND    c.type# not in (8, 12)
order by 1;

--decode(c.type#, 1, 'Check', 2, 'Primary Key', 3, 'Unique Key',4, 'Referential Integrity', 5, 'Check Option on a View',
--6, 'Read Only Option on a View', 7,'Check', '?')
--可以確定顯示來自這裡。

select c.type#
FROM   SYS.CDEF$ C, SYS.CON$ CN, SYS."_CURRENT_EDITION_OBJ" O, SYS.USER$ U,
       SYS.CON$ RC, SYS.USER$ RU, SYS."_CURRENT_EDITION_OBJ" RO
WHERE  C.CON# = CN.CON#
      AND    C.OBJ# = O.OBJ#
      AND    O.OWNER# = U.USER#
      AND    C.RCON# = RC.CON#(+)
      AND    RC.OWNER# = RU.USER#(+)
      AND    C.ROBJ# = RO.OBJ#(+)
      AND    U.NAME = 'SCOTT'
      AND    O.NAME = 'T';

     TYPE#
----------
        17

--可以發現顯示的是17，表示什麼呢？
SCOTT@test01p> @desc SYS.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(1000)
SPARE5               VARCHAR2(1000)
SPARE6               DATE

-- 看看安裝的執行指令碼：
cd D:\app\oracle\product\12.1.0\dbhome_1\RDBMS\admin
grep -i "cdef\$" *.* | grep -i "create table"

dcore.bsq:create table cdef$                            /* constraint definition table */

--檢視dcore.bsq檔案：
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 */
...

--很明顯17表示All column supplemental logging,才想起來前幾天學習goldengate，對開啟這個表執行了。
alter table t add supplemental log data (all) columns;

--執行如下：
alter table t drop supplemental log data (all) columns;

再檢視顯示正常了。