VIEW: SYS.DBA_LOGSTDBY_NOT_UNIQUE

Source

View Text - Preformatted

select owner, name table_name,
         decode((select count(c.obj#)
                 from sys.col$ c
                 where c.obj# = l.obj#
                 and ((c.type# in (8,                             /* LONG */
                                   24,                        /* LONG RAW */
                                   58,                             /* XML */
                                   112,                           /* CLOB */
                                   113))                          /* BLOB */
                 or (c.type# = 1 and bitand(c.property, 128) = 128))),
                 /* 32k varchar */
                 0, 'N', 'Y') bad_column
  from table(logstdby$tabf) l, tab$ t
  where gensby = 1
    and l.type# = 2
    and l.obj# = t.obj# and
        bitand(t.property, 1) = 0                    /* rule out typed table */
    and not exists                    /* not null unique key -- condition #1 */
       (select null -- (tagA)
        from ind$ i, icol$ ic, col$ c
        where i.bo# = l.obj#
          and ic.obj# = i.obj#
          and c.col# = ic.col#
          and c.obj# = i.bo#
          and c.null$ > 0                                       /* not null */
          and i.type# = 1                                          /* Btree */
          and bitand(i.property, 1) = 1                           /* Unique */
          and i.intcols = i.cols                      /* no virtual columns */
          and not exists (select null
                          from icol$ icol2, col$ col2
                          where  icol2.obj# = i.obj# and
                                 icol2.bo#  = i.bo#  and -- redundant
                                 icol2.bo#  = col2.obj# and
                                 icol2.intcol# = col2.intcol# and
                                 bitand(col2.property, 168) != 0)) -- (tagA)
    and not exists               /* primary key constraint --  condition #2 */
       (select null                         /* defer bit 0x1: deferrable    */
        from cdef$ cd                       /*       bit 0x4: sys validated */
        where cd.obj# = l.obj#              /*       bit 0x20: rely         */
          and cd.type# = 2
          and bitand(cd.defer, 37) in (4, 32, 36)
          and not exists (select null
                          from ccol$ ccol3, col$ col3
                          where ccol3.con# = cd.con# and
                                ccol3.obj# = cd.obj# and
                                ccol3.obj# = col3.obj# and
                                ccol3.intcol# = col3.intcol# and
                                bitand(col3.property, 168) != 0)
        )

View Text - HTML Formatted

SELECT OWNER
, NAME TABLE_NAME
, DECODE((SELECT COUNT(C.OBJ#)
FROM SYS.COL$ C
WHERE C.OBJ# = L.OBJ#
AND ((C.TYPE# IN (8
, /* LONG */ 24
, /* LONG RAW */ 58
, /* XML */ 112
, /* CLOB */ 113)) /* BLOB */ OR (C.TYPE# = 1
AND BITAND(C.PROPERTY
, 128) = 128)))
, /* 32K VARCHAR */ 0
, 'N'
, 'Y') BAD_COLUMN
FROM TABLE(LOGSTDBY$TABF) L
, TAB$ T
WHERE GENSBY = 1
AND L.TYPE# = 2
AND L.OBJ# = T.OBJ# AND BITAND(T.PROPERTY
, 1) = 0 /* RULE OUT TYPED TABLE */
AND NOT EXISTS /* NOT NULL UNIQUE KEY -- CONDITION #1 */ (SELECT NULL -- (TAGA)
FROM IND$ I
, ICOL$ IC
, COL$ C
WHERE I.BO# = L.OBJ#
AND IC.OBJ# = I.OBJ#
AND C.COL# = IC.COL#
AND C.OBJ# = I.BO#
AND C.NULL$ > 0 /* NOT NULL */
AND I.TYPE# = 1 /* BTREE */
AND BITAND(I.PROPERTY
, 1) = 1 /* UNIQUE */
AND I.INTCOLS = I.COLS /* NO VIRTUAL COLUMNS */
AND NOT EXISTS (SELECT NULL
FROM ICOL$ ICOL2
, COL$ COL2
WHERE ICOL2.OBJ# = I.OBJ# AND ICOL2.BO# = I.BO#
AND -- REDUNDANT ICOL2.BO# = COL2.OBJ# AND ICOL2.INTCOL# = COL2.INTCOL# AND BITAND(COL2.PROPERTY
, 168) != 0)) -- (TAGA)
AND NOT EXISTS /* PRIMARY KEY CONSTRAINT -- CONDITION #2 */ (SELECT NULL /* DEFER BIT 0X1: DEFERRABLE */
FROM CDEF$ CD /* BIT 0X4: SYS VALIDATED */
WHERE CD.OBJ# = L.OBJ# /* BIT 0X20: RELY */
AND CD.TYPE# = 2
AND BITAND(CD.DEFER
, 37) IN (4
, 32
, 36)
AND NOT EXISTS (SELECT NULL
FROM CCOL$ CCOL3
, COL$ COL3
WHERE CCOL3.CON# = CD.CON# AND CCOL3.OBJ# = CD.OBJ# AND CCOL3.OBJ# = COL3.OBJ# AND CCOL3.INTCOL# = COL3.INTCOL# AND BITAND(COL3.PROPERTY
, 168) != 0) )