VIEW: SYS.INT$INT$DBA_CONSTRAINTS

Source

View Text - Preformatted

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, o.obj#, o.type#, c.condition,
       getlong(2, c.rowid),
       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),
       decode(bitand(o.flags, 196608), 65536, 1, 131072, 1, 0),
       to_number(sys_context('USERENV', 'CON_ID'))
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#(+)

View Text - HTML Formatted

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
, O.OBJ#
, O.TYPE#
, C.CONDITION
, GETLONG(2
, C.ROWID)
, 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)
, DECODE(BITAND(O.FLAGS
, 196608)
, 65536
, 1
, 131072
, 1
, 0)
, TO_NUMBER(SYS_CONTEXT('USERENV'
, 'CON_ID')) 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#(+)