[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;
再查看显示正常了。