VIEW: SYS.ALL_DIM_LEVELS

Source

View Text - Preformatted

select u.name, o.name, dl.levelname,
       temp.num_col,
       u1.name, o1.name, decode (dl.flags, 1, 'Y', 'N')
from (select dlk.dimobj#, dlk.levelid#, dlk.detailobj#,
             COUNT(*) as num_col
      from sys.dimlevelkey$ dlk
      group by dlk.dimobj#, dlk.levelid#, dlk.detailobj#) temp,
      sys.dimlevel$ dl, sys.obj$ o, sys.user$ u,
      sys.obj$ o1, sys.user$ u1
where dl.dimobj# = o.obj#   and
      o.owner# = u.user#    and
      dl.dimobj# = temp.dimobj# and
      dl.levelid# = temp.levelid# and
      temp.detailobj# = o1.obj# and
      o1.owner# = u1.user# 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 (-215 /* CREATE ANY DIMENSION */,
                                       -216 /* ALTER ANY DIMENSION */,
                                       -217 /* DROP ANY DIMENSION */)
                 )
      )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, DL.LEVELNAME
, TEMP.NUM_COL
, U1.NAME
, O1.NAME
, DECODE (DL.FLAGS
, 1
, 'Y'
, 'N') FROM (SELECT DLK.DIMOBJ#
, DLK.LEVELID#
, DLK.DETAILOBJ#
, COUNT(*) AS NUM_COL
FROM SYS.DIMLEVELKEY$ DLK GROUP BY DLK.DIMOBJ#
, DLK.LEVELID#
, DLK.DETAILOBJ#) TEMP
, SYS.DIMLEVEL$ DL
, SYS.OBJ$ O
, SYS.USER$ U
, SYS.OBJ$ O1
, SYS.USER$ U1 WHERE DL.DIMOBJ# = O.OBJ# AND O.OWNER# = U.USER# AND DL.DIMOBJ# = TEMP.DIMOBJ# AND DL.LEVELID# = TEMP.LEVELID# AND TEMP.DETAILOBJ# = O1.OBJ# AND O1.OWNER# = U1.USER# 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 (-215 /* CREATE ANY DIMENSION */
, -216 /* ALTER ANY DIMENSION */
, -217 /* DROP ANY DIMENSION */) ) )