[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;

再查看显示正常了。

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

转载于:http://blog.itpub.net/267265/viewspace-1076597/