VIEW: SYS.USER_SUBPART_KEY_COLUMNS

Source

View Text - Preformatted

select o.name, 'TABLE',
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, attrcol$ a
where  spc.obj# = o.obj# and spc.obj# = c.obj#
       and c.intcol# = spc.intcol# and o.owner# = userenv('SCHEMAID')
       and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
       and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
       and o.subname IS NULL
union
select o.name, 'INDEX',
  decode(bitand(c.property, 1), 1, a.name, c.name), spc.pos#
from   obj$ o, subpartcol$ spc, col$ c, ind$ i, attrcol$ a
where  spc.obj# = i.obj# and i.obj# = o.obj# and i.bo# = c.obj#
       and c.intcol# = spc.intcol# and o.owner# = userenv('SCHEMAID')
       and c.obj# = a.obj#(+) and c.intcol# = a.intcol#(+)
       and o.namespace = 4 and o.remoteowner IS NULL and o.linkname IS NULL
       and o.subname IS NULL

View Text - HTML Formatted

SELECT O.NAME
, 'TABLE'
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, SPC.POS# FROM OBJ$ O
, SUBPARTCOL$ SPC
, COL$ C
, ATTRCOL$ A WHERE SPC.OBJ# = O.OBJ#
AND SPC.OBJ# = C.OBJ#
AND C.INTCOL# = SPC.INTCOL#
AND O.OWNER# = USERENV('SCHEMAID')
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+)
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND O.SUBNAME IS NULL UNION SELECT O.NAME
, 'INDEX'
, DECODE(BITAND(C.PROPERTY
, 1)
, 1
, A.NAME
, C.NAME)
, SPC.POS# FROM OBJ$ O
, SUBPARTCOL$ SPC
, COL$ C
, IND$ I
, ATTRCOL$ A WHERE SPC.OBJ# = I.OBJ#
AND I.OBJ# = O.OBJ#
AND I.BO# = C.OBJ#
AND C.INTCOL# = SPC.INTCOL#
AND O.OWNER# = USERENV('SCHEMAID')
AND C.OBJ# = A.OBJ#(+)
AND C.INTCOL# = A.INTCOL#(+)
AND O.NAMESPACE = 4
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL
AND O.SUBNAME IS NULL