VIEW: SYS.DBA_NESTED_TABLES

Source

View Text - Preformatted

select u.name, o.name,
       nvl2(ct.synobj#, (select u.name from "_BASE_USER" u, obj$ o
            where o.owner#=u.user# and o.obj#=ct.synobj#), ut.name),
       nvl2(ct.synobj#, (select o.name from obj$ o where o.obj#=ct.synobj#),
            ot.name),
       op.name, ac.name,
       lpad(decode(bitand(ct.flags, 64), 64, 'USER_SPECIFIED', 'DEFAULT'), 30),
       lpad(decode(bitand(ct.flags, 32), 32, 'LOCATOR', 'VALUE'), 20),
       lpad((case when bitand(ct.flags, 5120)=0 and bitand(t.properties, 8)= 8
       then 'Y' else 'N' end), 25)
from sys.ntab$ n, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" op,
  sys.obj$ ot, sys.col$ c, sys.coltype$ ct, sys.user$ u,
  sys."_BASE_USER" ut, sys.attrcol$ ac, sys.type$ t, sys.collection$ cl
where o.owner# = u.user#
  and op.owner# = u.user#
  and n.obj# = op.obj#
  and n.ntab# = o.obj#
  and c.obj# = op.obj#
  and n.intcol# = c.intcol#
  and c.obj# = ac.obj#
  and c.intcol# = ac.intcol#
  and op.obj# = ct.obj#
  and ct.toid = ot.oid$
  and ct.intcol#=n.intcol#
  and ot.owner# = ut.user#
  and ct.toid=cl.toid
  and cl.elem_toid=t.tvoid
  and bitand(ct.flags,4)=4
  and bitand(c.property,32768) != 32768           /* not unused column */
  and bitand(o.flags,128) = 0                     /* not in recycle bin */
union all
select u.name, o.name,
       nvl2(ct.synobj#, (select u.name from "_BASE_USER" u, obj$ o
            where o.owner#=u.user# and o.obj#=ct.synobj#), ut.name),
       nvl2(ct.synobj#, (select o.name from obj$ o where o.obj#=ct.synobj#),
            ot.name),
       op.name, c.name,
       lpad(decode(bitand(ct.flags, 64), 64, 'USER_SPECIFIED', 'DEFAULT'), 30),
       lpad(decode(bitand(ct.flags, 32), 32, 'LOCATOR', 'VALUE'), 20),
       lpad((case when bitand(ct.flags, 5120)=0 and bitand(t.properties, 8)= 8
       then 'Y' else 'N' end), 25)
from sys.ntab$ n, sys."_CURRENT_EDITION_OBJ" o, sys."_CURRENT_EDITION_OBJ" op,
  sys.obj$ ot, sys.col$ c, sys.coltype$ ct, sys.user$ u,
  sys."_BASE_USER" ut, sys.type$ t, sys.collection$ cl
where o.owner# = u.user#
  and op.owner# = u.user#
  and n.obj# = op.obj#
  and n.ntab# = o.obj#
  and c.obj# = op.obj#
  and n.intcol# = c.intcol#
  and bitand(c.property,1)=0
  and op.obj# = ct.obj#
  and ct.toid = ot.oid$
  and ct.intcol#=n.intcol#
  and ot.owner# = ut.user#
  and ct.toid=cl.toid
  and cl.elem_toid=t.tvoid
  and bitand(ct.flags,4)=4
  and bitand(c.property,32768) != 32768           /* not unused column */
  and bitand(o.flags,128) = 0                     /* not in recycle bin */

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, NVL2(CT.SYNOBJ#
, (SELECT U.NAME
FROM "_BASE_USER" U
, OBJ$ O
WHERE O.OWNER#=U.USER#
AND O.OBJ#=CT.SYNOBJ#)
, UT.NAME)
, NVL2(CT.SYNOBJ#
, (SELECT O.NAME
FROM OBJ$ O
WHERE O.OBJ#=CT.SYNOBJ#)
, OT.NAME)
, OP.NAME
, AC.NAME
, LPAD(DECODE(BITAND(CT.FLAGS
, 64)
, 64
, 'USER_SPECIFIED'
, 'DEFAULT')
, 30)
, LPAD(DECODE(BITAND(CT.FLAGS
, 32)
, 32
, 'LOCATOR'
, 'VALUE')
, 20)
, LPAD((CASE WHEN BITAND(CT.FLAGS
, 5120)=0
AND BITAND(T.PROPERTIES
, 8)= 8 THEN 'Y' ELSE 'N' END)
, 25) FROM SYS.NTAB$ N
, SYS."_CURRENT_EDITION_OBJ" O
, SYS."_CURRENT_EDITION_OBJ" OP
, SYS.OBJ$ OT
, SYS.COL$ C
, SYS.COLTYPE$ CT
, SYS.USER$ U
, SYS."_BASE_USER" UT
, SYS.ATTRCOL$ AC
, SYS.TYPE$ T
, SYS.COLLECTION$ CL WHERE O.OWNER# = U.USER#
AND OP.OWNER# = U.USER#
AND N.OBJ# = OP.OBJ#
AND N.NTAB# = O.OBJ#
AND C.OBJ# = OP.OBJ#
AND N.INTCOL# = C.INTCOL#
AND C.OBJ# = AC.OBJ#
AND C.INTCOL# = AC.INTCOL#
AND OP.OBJ# = CT.OBJ#
AND CT.TOID = OT.OID$
AND CT.INTCOL#=N.INTCOL#
AND OT.OWNER# = UT.USER#
AND CT.TOID=CL.TOID
AND CL.ELEM_TOID=T.TVOID
AND BITAND(CT.FLAGS
, 4)=4
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND BITAND(O.FLAGS
, 128) = 0 /* NOT IN RECYCLE BIN */ UNION ALL SELECT U.NAME
, O.NAME
, NVL2(CT.SYNOBJ#
, (SELECT U.NAME
FROM "_BASE_USER" U
, OBJ$ O
WHERE O.OWNER#=U.USER#
AND O.OBJ#=CT.SYNOBJ#)
, UT.NAME)
, NVL2(CT.SYNOBJ#
, (SELECT O.NAME
FROM OBJ$ O
WHERE O.OBJ#=CT.SYNOBJ#)
, OT.NAME)
, OP.NAME
, C.NAME
, LPAD(DECODE(BITAND(CT.FLAGS
, 64)
, 64
, 'USER_SPECIFIED'
, 'DEFAULT')
, 30)
, LPAD(DECODE(BITAND(CT.FLAGS
, 32)
, 32
, 'LOCATOR'
, 'VALUE')
, 20)
, LPAD((CASE WHEN BITAND(CT.FLAGS
, 5120)=0
AND BITAND(T.PROPERTIES
, 8)= 8 THEN 'Y' ELSE 'N' END)
, 25) FROM SYS.NTAB$ N
, SYS."_CURRENT_EDITION_OBJ" O
, SYS."_CURRENT_EDITION_OBJ" OP
, SYS.OBJ$ OT
, SYS.COL$ C
, SYS.COLTYPE$ CT
, SYS.USER$ U
, SYS."_BASE_USER" UT
, SYS.TYPE$ T
, SYS.COLLECTION$ CL WHERE O.OWNER# = U.USER#
AND OP.OWNER# = U.USER#
AND N.OBJ# = OP.OBJ#
AND N.NTAB# = O.OBJ#
AND C.OBJ# = OP.OBJ#
AND N.INTCOL# = C.INTCOL#
AND BITAND(C.PROPERTY
, 1)=0
AND OP.OBJ# = CT.OBJ#
AND CT.TOID = OT.OID$
AND CT.INTCOL#=N.INTCOL#
AND OT.OWNER# = UT.USER#
AND CT.TOID=CL.TOID
AND CL.ELEM_TOID=T.TVOID
AND BITAND(CT.FLAGS
, 4)=4
AND BITAND(C.PROPERTY
, 32768) != 32768 /* NOT UNUSED COLUMN */
AND BITAND(O.FLAGS
, 128) = 0 /* NOT IN RECYCLE BIN */