VIEW: OLAPSYS.ALL$OLAPMR_DIM_LEVELS

Source

View Text - Preformatted

SELECT /*+ORDERED*/
  u.username                             owner,
  d.name                             dimension_name,
  h.hiername                         hierarchy_name,
  dl.levelname                       level_name,
  NVL(lev.displayname,dl.levelname)  display_name,
  NVL(lev.description,dl.levelname)  short_description,
  NVL(lev.description,dl.levelname)  description,
  hl.pos#                            pos
FROM
  sys.obj$          d,
  dba_users         u,
  sys.hier$         h,
  sys.hierlevel$    hl,
  sys.dimlevel$     dl,
  sys.dimlevelkey$  k,
  sys.obj$          t,
  dba_users         tu,
  cwm$level         lev
WHERE
  d.type#      = 43 AND              /* DIMENSION */
 (   cwm$util.dimension_tables_visible(d.obj#) = 'Y'
     OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */
       (SELECT null FROM v$enabledprivs
        WHERE priv_number IN (-47,-215,-216,-217)))
AND
  u.user_id    = d.owner# AND
  d.obj#       = h.dimobj# AND
  d.obj#       = hl.dimobj# AND
  h.hierid#    = hl.hierid# AND
  hl.dimobj#   = dl.dimobj# AND
  hl.levelid#  = dl.levelid# AND
  dl.dimobj#   = k.dimobj# AND
  dl.levelid#  = k.levelid# AND
  k.keypos#    = 1 AND          /* Restrict composite keys to 1 rec */
  k.detailobj# = t.obj# AND
  t.owner#     = tu.user_id AND
  dl.dimobj#   = lev.dimension_irid AND
  dl.levelname = lev.physicalname (+)
ORDER BY
  owner ASC,
  dimension_name ASC,
  hierarchy_name ASC,
  pos DESC

View Text - HTML Formatted

SELECT /*+ORDERED*/ U.USERNAME OWNER
, D.NAME DIMENSION_NAME
, H.HIERNAME HIERARCHY_NAME
, DL.LEVELNAME LEVEL_NAME
, NVL(LEV.DISPLAYNAME
, DL.LEVELNAME) DISPLAY_NAME
, NVL(LEV.DESCRIPTION
, DL.LEVELNAME) SHORT_DESCRIPTION
, NVL(LEV.DESCRIPTION
, DL.LEVELNAME) DESCRIPTION
, HL.POS# POS FROM SYS.OBJ$ D
, DBA_USERS U
, SYS.HIER$ H
, SYS.HIERLEVEL$ HL
, SYS.DIMLEVEL$ DL
, SYS.DIMLEVELKEY$ K
, SYS.OBJ$ T
, DBA_USERS TU
, CWM$LEVEL LEV WHERE D.TYPE# = 43
AND /* DIMENSION */ ( CWM$UTIL.DIMENSION_TABLES_VISIBLE(D.OBJ#) = 'Y' OR EXISTS /* SELECT ANY TABLE
, CREATE
, ALTER
, DROP ANY DIMENSION */ (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217))) AND U.USER_ID = D.OWNER# AND D.OBJ# = H.DIMOBJ# AND D.OBJ# = HL.DIMOBJ# AND H.HIERID# = HL.HIERID# AND HL.DIMOBJ# = DL.DIMOBJ# AND HL.LEVELID# = DL.LEVELID# AND DL.DIMOBJ# = K.DIMOBJ# AND DL.LEVELID# = K.LEVELID# AND K.KEYPOS# = 1
AND /* RESTRICT COMPOSITE KEYS TO 1 REC */ K.DETAILOBJ# = T.OBJ# AND T.OWNER# = TU.USER_ID AND DL.DIMOBJ# = LEV.DIMENSION_IRID AND DL.LEVELNAME = LEV.PHYSICALNAME (+) ORDER BY OWNER ASC
, DIMENSION_NAME ASC
, HIERARCHY_NAME ASC
, POS DESC