Oracle DB 11.2.0.4 中SYS.ALL_CONSTRAINTS的定義 和 SYS.ALL_CONS_COLUMNS的定義，

Oracle DB 11.2.0.4 中SYS.ALL_CONSTRAINTS的定義 和 SYS.ALL_CONS_COLUMNS的定義，

SYS.ALL_CONSTRAINTS的定義如下：

CREATE OR REPLACE VIEW ALL_CONSTRAINTS(owner, constraint_name, constraint_type, table_name, search_condition, r_owner, r_constraint_name, delete_rule, status, deferrable, deferred, validated, generated, bad, rely, last_change, index_owner, index_name, invalid, view_related)ASselect 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$ uiwhere oc.owner# = ou.user#  and oc.con# = c.con#  and c.obj# = o.obj#  and c.type# != 8  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#(+)  and (o.owner# = userenv('SCHEMAID')       or o.obj# in (select obj#                     from sys.objauth$                     where grantee# in ( select kzsrorol                                         from x$kzsro                                       )                    )        or /* user has system privileges */          exists (select null from v$enabledprivs                  where priv_number in (-45 /* LOCK ANY TABLE */,                                        -47 /* SELECT ANY TABLE */,                                        -48 /* INSERT ANY TABLE */,                                        -49 /* UPDATE ANY TABLE */,                                        -50 /* DELETE ANY TABLE */)                  )      )

 

SYS.ALL_CONS_COLUMNS的定義如下：

CREATE OR REPLACE VIEW ALL_CONS_COLUMNS(owner, constraint_name, table_name, column_name, position)ASselect u.name, c.name, o.name,       decode(ac.name, null, col.name, ac.name), cc.pos#from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,     sys."_CURRENT_EDITION_OBJ" o, sys.attrcol$ acwhere c.owner# = u.user#  and c.con# = cd.con#  and (cd.type# < 14 or cd.type# > 17)   /* don't include supplog cons   */  and (cd.type# != 12)                   /* don't include log group cons */  and cd.con# = cc.con#  and cc.obj# = col.obj#  and cc.intcol# = col.intcol#  and cc.obj# = o.obj#  and (c.owner# = userenv('SCHEMAID')       or cd.obj# in (select obj#                      from sys.objauth$                      where grantee# in ( select kzsrorol                                         from x$kzsro                                       )                     )        or /* user has system privileges */          exists (select null from v$enabledprivs                  where priv_number in (-45 /* LOCK ANY TABLE */,                                        -47 /* SELECT ANY TABLE */,                                        -48 /* INSERT ANY TABLE */,                                        -49 /* UPDATE ANY TABLE */,                                        -50 /* DELETE ANY TABLE */)                  )      )  and col.obj# = ac.obj#(+)  and col.intcol# = ac.intcol#(+)