VIEW: SYS.ALL_TAB_IDENTITY_COLS

Source

View Text - Preformatted

select u.name, o.name, c.name,
       decode(bitand(c.property, 137438953472 + 274877906944),
                     137438953472, 'ALWAYS',
                     274877906944, 'BY DEFAULT'),
       so.name,
       'START WITH: '     || i.startwith ||
       ', INCREMENT BY: ' || s.increment$ ||
       ', MAX_VALUE: '    || s.maxvalue ||
       ', MIN_VALUE: '    || s.minvalue ||
       ', CYCLE_FLAG: '   || decode (s.cycle#, 0, 'N', 1, 'Y') ||
       ', CACHE_SIZE: '   || s.cache ||
       ', ORDER_FLAG: '   || decode (s.order$, 0, 'N', 1, 'Y')
from sys.idnseq$ i, sys.obj$ o, sys.user$ u, sys.col$ c,
     sys.seq$ s, sys.obj$ so
where o.owner# = u.user#
and o.obj# = i.obj#
and c.intcol# = i.intcol#
and c.obj# = i.obj#
and s.obj# = i.seqobj#
and so.obj# = i.seqobj#
and (o.owner# = userenv('SCHEMAID')
     or o.obj# in
          (select oa.obj#
           from sys.objauth$ oa
           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 */,
                                     -397/* READ ANY TABLE */,
                                     -48 /* INSERT ANY TABLE */,
                                     -49 /* UPDATE ANY TABLE */,
                                     -50 /* DELETE ANY TABLE */)
               )
    )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, C.NAME
, DECODE(BITAND(C.PROPERTY
, 137438953472 + 274877906944)
, 137438953472
, 'ALWAYS'
, 274877906944
, 'BY DEFAULT')
, SO.NAME
, 'START WITH: ' || I.STARTWITH || '
, INCREMENT BY: ' || S.INCREMENT$ || '
, MAX_VALUE: ' || S.MAXVALUE || '
, MIN_VALUE: ' || S.MINVALUE || '
, CYCLE_FLAG: ' || DECODE (S.CYCLE#
, 0
, 'N'
, 1
, 'Y') || '
, CACHE_SIZE: ' || S.CACHE || '
, ORDER_FLAG: ' || DECODE (S.ORDER$
, 0
, 'N'
, 1
, 'Y') FROM SYS.IDNSEQ$ I
, SYS.OBJ$ O
, SYS.USER$ U
, SYS.COL$ C
, SYS.SEQ$ S
, SYS.OBJ$ SO WHERE O.OWNER# = U.USER# AND O.OBJ# = I.OBJ# AND C.INTCOL# = I.INTCOL# AND C.OBJ# = I.OBJ# AND S.OBJ# = I.SEQOBJ# AND SO.OBJ# = I.SEQOBJ# AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
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 */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) )