[20141029]10g和11G檢視DBA_CONSTRAINTS

[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# < 14 or 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# < 14 or 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