VIEW: SYS.ALL_ASSOCIATIONS

Source

View Text - Preformatted

select u.name, o.name, c.name,
         decode(a.property, 1, 'COLUMN', 2, 'TYPE', 3, 'PACKAGE', 4,
                'FUNCTION', 5, 'INDEX', 6, 'INDEXTYPE', 'INVALID'),
         u1.name, o1.name,a.default_selectivity,
         a.default_cpu_cost, a.default_io_cost, a.default_net_cost,
         a.interface_version#,
         decode (bitand(a.spare2, 1), 1, 'SYSTEM_MANAGED', 'USER_MANAGED')
   from  sys.association$ a, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u,
         sys."_CURRENT_EDITION_OBJ" o1, sys.user$ u1, sys.col$ c
   where a.obj#=o.obj# and o.owner#=u.user#
   AND   a.statstype#=o1.obj# (+) and o1.owner#=u1.user# (+)
   AND   a.obj# = c.obj#  (+)  and a.intcol# = c.intcol# (+)
   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
       ( o.type# in (2)  /* table */
         and
         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 */,
                                        -42 /* ALTER ANY TABLE */)
                 )
       )
       or
       ( o.type# in (8, 9)   /* package or function */
         and
         exists (select null from v$enabledprivs
                  where priv_number in (-140 /* CREATE PROCEDURE */,
                                        -141 /* CREATE ANY PROCEDURE */,
                                        -142 /* ALTER ANY PROCEDURE */,
                                        -143 /* DROP ANY PROCEDURE */,
                                        -144 /* EXECUTE ANY PROCEDURE */)
                 )
       )
       or
       ( o.type# in (13)     /* type */
         and
         exists (select null from v$enabledprivs
                  where priv_number in (-180 /* CREATE TYPE */,
                                        -181 /* CREATE ANY TYPE */,
                                        -182 /* ALTER ANY TYPE */,
                                        -183 /* DROP ANY TYPE */,
                                        -184 /* EXECUTE ANY TYPE */)
                 )
       )
       or
       ( o.type# in (1)     /* index */
         and
         exists (select null from v$enabledprivs
                  where priv_number in (-71 /* CREATE ANY INDEX */,
                                        -72 /* ALTER ANY INDEX */,
                                        -73 /* DROP ANY INDEX */)
                 )
       )
       or
       ( o.type# in (32)     /* indextype */
         and
         exists (select null from v$enabledprivs
                  where priv_number in (-205 /* CREATE INDEXTYPE */,
                                        -206 /* CREATE ANY INDEXTYPE */,
                                        -207 /* ALTER ANY INDEXTYPE */,
                                        -208 /* DROP ANY INDEXTYPE */)
                 )
       )
    )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, C.NAME
, DECODE(A.PROPERTY
, 1
, 'COLUMN'
, 2
, 'TYPE'
, 3
, 'PACKAGE'
, 4
, 'FUNCTION'
, 5
, 'INDEX'
, 6
, 'INDEXTYPE'
, 'INVALID')
, U1.NAME
, O1.NAME
, A.DEFAULT_SELECTIVITY
, A.DEFAULT_CPU_COST
, A.DEFAULT_IO_COST
, A.DEFAULT_NET_COST
, A.INTERFACE_VERSION#
, DECODE (BITAND(A.SPARE2
, 1)
, 1
, 'SYSTEM_MANAGED'
, 'USER_MANAGED')
FROM SYS.ASSOCIATION$ A
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS."_CURRENT_EDITION_OBJ" O1
, SYS.USER$ U1
, SYS.COL$ C
WHERE A.OBJ#=O.OBJ#
AND O.OWNER#=U.USER#
AND A.STATSTYPE#=O1.OBJ# (+)
AND O1.OWNER#=U1.USER# (+)
AND A.OBJ# = C.OBJ# (+)
AND A.INTCOL# = C.INTCOL# (+)
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 ( O.TYPE# IN (2) /* TABLE */ AND 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 */
, -42 /* ALTER ANY TABLE */) ) ) OR ( O.TYPE# IN (8
, 9) /* PACKAGE OR FUNCTION */ AND EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-140 /* CREATE PROCEDURE */
, -141 /* CREATE ANY PROCEDURE */
, -142 /* ALTER ANY PROCEDURE */
, -143 /* DROP ANY PROCEDURE */
, -144 /* EXECUTE ANY PROCEDURE */) ) ) OR ( O.TYPE# IN (13) /* TYPE */ AND EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-180 /* CREATE TYPE */
, -181 /* CREATE ANY TYPE */
, -182 /* ALTER ANY TYPE */
, -183 /* DROP ANY TYPE */
, -184 /* EXECUTE ANY TYPE */) ) ) OR ( O.TYPE# IN (1) /* INDEX */ AND EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-71 /* CREATE ANY INDEX */
, -72 /* ALTER ANY INDEX */
, -73 /* DROP ANY INDEX */) ) ) OR ( O.TYPE# IN (32) /* INDEXTYPE */ AND EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-205 /* CREATE INDEXTYPE */
, -206 /* CREATE ANY INDEXTYPE */
, -207 /* ALTER ANY INDEXTYPE */
, -208 /* DROP ANY INDEXTYPE */) ) ) )