[20141029]10g和11G视图DBA_CONSTRAINTS.txt
--上午同事讲一下表从10g导入11g时,一些约束没有导入,感觉不可能出现这种情况,我在10g下查看一些约束的状态是是disabled,但是
--type显示的是问号(在toad下).想起来我以前遇到的问题,
--[20140131]toad看constraints的问题.txt
http://blog.itpub.net/267265/viewspace-1076597/
--很容易明白这些表是没有主键,不过在11g下查看视图DBA_CONSTRAINTS确实没有这些约束,查看视图定义才发现11g下DBA_CONSTRAINTS的
--定义发生了变化:
SYS@test> select text from dba_views where view_name='DBA_CONSTRAINTS';
TEXT
-------------------------------------------------------------------------------
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.type# != 8 /* don't include hash expressions */
and (c.type# 17) /* don't include supplog cons */
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
and (c.type# != 12) /* don't include log group cons */
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#(+)
--注意~的部分.(c.type# 17)不显示.如果查看10g的定义没有这些部分.
$ grep -i 'cdef\$' * | grep -i 'create table cdef'
dcore.bsq:create table cdef$ /* constraint definition table */
create index i_cobj# on cluster c_cobj#
/
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),
pare5 varchar2(1000),
spare6 date
)
cluster c_cobj#(obj#)
/
--做一个简单的测试:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table ttx
( a number(1) not null,
b number(1),
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
);
SCOTT@test> select dbms_metadata.get_ddl('TABLE', 'TTX') from dual ;
DBMS_METADATA.GET_DDL('TABLE','TTX')
-----------------------------------------------------------------------
CREATE TABLE "SCOTT"."TTX"
( "A" NUMBER(1,0) NOT NULL ENABLE,
"B" NUMBER(1,0),
SUPPLEMENTAL LOG DATA (ALL) COLUMNS
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
SCOTT@test> select owner,CONSTRAINT_NAME from DBA_CONSTRAINTS where owner=user and table_name='TTX';
OWNER CONSTRAINT_NAME
------ ------------------------------
SCOTT SYS_C0042410
--可以发现仅仅有1个约束.
SELECT CON#,
OBJ#,
COLS,
TYPE#,
ROBJ#,
RCON#,
RRULES,
MATCH#,
REFACT,
ENABLED,
CONDLENGTH,
CONDITION
FROM sys.cdef$
WHERE obj# IN (SELECT object_id
FROM dba_objects
WHERE object_name = 'TTX' AND owner = USER);
CON# OBJ# COLS TYPE# ROBJ# RCON# RRU MATCH# REFACT ENABLED CONDLENGTH CONDITION
----- ---------- ---------- ---------- ---------- ---------- --- ---------- ---------- ---------- ---------- -----------------
42410 292348 1 7 1 15 "A" IS NOT NULL
42411 292348 0 17
-- 可以发现实际上有2个的,type#=17的在DBA_CONSTRAINTS视图就不显示了.
SCOTT@test> alter table ttx drop supplemental log data (all) columns;
Table altered.
SCOTT@test> select dbms_metadata.get_ddl('TABLE', 'TTX') from dual ;
DBMS_METADATA.GET_DDL('TABLE','TTX')
----------------------------------------------------------------------------
CREATE TABLE "SCOTT"."TTX"
( "A" NUMBER(1,0) NOT NULL ENABLE,
"B" NUMBER(1,0)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
SELECT CON#,
OBJ#,
COLS,
TYPE#,
ROBJ#,
RCON#,
RRULES,
MATCH#,
REFACT,
ENABLED,
CONDLENGTH,
CONDITION
FROM sys.cdef$
WHERE obj# IN (SELECT object_id
FROM dba_objects
WHERE object_name = 'TTX' AND owner = USER);
CON# OBJ# COLS TYPE# ROBJ# RCON# RRU MATCH# REFACT ENABLED CONDLENGTH CONDITION
----- ---------- ---------- ---------- ---------- ---------- --- ---------- ---------- ---------- ---------- ----------------------------------------
42410 292348 1 7 1 15 "A" IS NOT NULL