VIEW: SYS.KU$_CONSTRAINT1_VIEW

Source

View Text - Preformatted

select c.owner#, c.name, c.con#, cd.obj#,
          nvl((select bitand(t.property, 4294967295)
               from tab$ t where t.obj# = cd.obj#),0),
          nvl((select trunc(t.property / power(2, 32))
               from tab$ t where t.obj# = cd.obj#),0),
          cd.cols, cd.type#,
          nvl(cd.enabled,0),
          cd.condlength,
          sys.dbms_metadata_util.long2clob(cd.condlength,
                                        'SYS.CDEF$',
                                        'CONDITION',
                                        cd.rowid),
          case when cd.type#=1 then
            (select sys.dbms_metadata.parse_condition(u.name,o.name,
                                                      cd.condlength,cd.rowid)
             from obj$ o, user$ u
             where o.obj#=cd.obj# and o.owner#=u.user#)
          else null end,
          cd.intcols, to_char(cd.mtime,'YYYY/MM/DD HH24:MI:SS'), nvl(cd.defer,0),
          nvl((select cc.oid_or_setid
               from ku$_constraint_col_view cc
                 where cd.type#=3
                 and cd.intcols=1
                 and cc.con_num=cd.con#),0),
          cast( multiset(select * from ku$_constraint_col_view col
                        where col.con_num = c.con#
                        order by col.pos_num
                        ) as ku$_constraint_col_list_t
                ),
          ( select value(i) from ku$_all_index_view i
                where i.obj_num=cd.enabled )
--              where i.schema_obj.owner_num=c.owner#
--                and i.schema_obj.name=c.name )
   from  con$ c, cdef$ cd
   where c.con# = cd.con#
     and cd.type# in (1,2,3,12,14,15,16,17)
                               -- table check (condition-no keys) (1),
                               -- primary key (2),
                               -- unique key (3),
                               -- supplemental log groups (w/ keys) (12),
                               -- supplemental log data (no keys) (14,15,16,17)

View Text - HTML Formatted

SELECT C.OWNER#
, C.NAME
, C.CON#
, CD.OBJ#
, NVL((SELECT BITAND(T.PROPERTY
, 4294967295)
FROM TAB$ T
WHERE T.OBJ# = CD.OBJ#)
, 0)
, NVL((SELECT TRUNC(T.PROPERTY / POWER(2
, 32))
FROM TAB$ T
WHERE T.OBJ# = CD.OBJ#)
, 0)
, CD.COLS
, CD.TYPE#
, NVL(CD.ENABLED
, 0)
, CD.CONDLENGTH
, SYS.DBMS_METADATA_UTIL.LONG2CLOB(CD.CONDLENGTH
, 'SYS.CDEF$'
, 'CONDITION'
, CD.ROWID)
, CASE WHEN CD.TYPE#=1 THEN (SELECT SYS.DBMS_METADATA.PARSE_CONDITION(U.NAME
, O.NAME
, CD.CONDLENGTH
, CD.ROWID)
FROM OBJ$ O
, USER$ U
WHERE O.OBJ#=CD.OBJ#
AND O.OWNER#=U.USER#) ELSE NULL END
, CD.INTCOLS
, TO_CHAR(CD.MTIME
, 'YYYY/MM/DD HH24:MI:SS')
, NVL(CD.DEFER
, 0)
, NVL((SELECT CC.OID_OR_SETID
FROM KU$_CONSTRAINT_COL_VIEW CC
WHERE CD.TYPE#=3
AND CD.INTCOLS=1
AND CC.CON_NUM=CD.CON#)
, 0)
, CAST( MULTISET(SELECT *
FROM KU$_CONSTRAINT_COL_VIEW COL
WHERE COL.CON_NUM = C.CON# ORDER BY COL.POS_NUM ) AS KU$_CONSTRAINT_COL_LIST_T )
, ( SELECT VALUE(I)
FROM KU$_ALL_INDEX_VIEW I
WHERE I.OBJ_NUM=CD.ENABLED ) --
WHERE I.SCHEMA_OBJ.OWNER_NUM=C.OWNER# --
AND I.SCHEMA_OBJ.NAME=C.NAME )
FROM CON$ C
, CDEF$ CD
WHERE C.CON# = CD.CON#
AND CD.TYPE# IN (1
, 2
, 3
, 12
, 14
, 15
, 16
, 17) -- TABLE CHECK (CONDITION-NO KEYS) (1)
, -- PRIMARY KEY (2)
, -- UNIQUE KEY (3)
, -- SUPPLEMENTAL LOG GROUPS (W/ KEYS) (12)
, -- SUPPLEMENTAL LOG DATA (NO KEYS) (14
, 15
, 16
, 17)