VIEW: OLAPSYS.ALL$OLAP9I1_HIER_DIMENSIONS

Source

View Text - Preformatted

SELECT distinct
  u.username              owner,
  o.name                  dimension_name,
  dim.pluralname          plural_name,
  dim.displayname         display_name,
  dim.description         short_description,
  dim.description         description,
  display_hierarchy_name  default_display_hierarchy,
  (case when ce.classification_irid = 28 then 'Time'
       else 'Other'
       end)    descriptor_value
FROM
  sys.dim$                d,
  dba_users               u,
  sys.obj$                o,
  olapsys.cwm$dimension           dim,
  sys.hier$               h,
  olapsys.cwm$classificationentry ce
WHERE o.type# = 43 AND
      u.user_id = o.owner# AND
      o.obj#  = d.obj# AND
      d.obj#  = dim.irid AND
      (   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
      o.obj# = h.dimobj#
AND dim.irid = ce.element_irid (+) and
    ce.name (+) = 'DIMENSION'
WITH READ ONLY

View Text - HTML Formatted

SELECT DISTINCT U.USERNAME OWNER
, O.NAME DIMENSION_NAME
, DIM.PLURALNAME PLURAL_NAME
, DIM.DISPLAYNAME DISPLAY_NAME
, DIM.DESCRIPTION SHORT_DESCRIPTION
, DIM.DESCRIPTION DESCRIPTION
, DISPLAY_HIERARCHY_NAME DEFAULT_DISPLAY_HIERARCHY
, (CASE WHEN CE.CLASSIFICATION_IRID = 28 THEN 'TIME' ELSE 'OTHER' END) DESCRIPTOR_VALUE FROM SYS.DIM$ D
, DBA_USERS U
, SYS.OBJ$ O
, OLAPSYS.CWM$DIMENSION DIM
, SYS.HIER$ H
, OLAPSYS.CWM$CLASSIFICATIONENTRY CE WHERE O.TYPE# = 43 AND U.USER_ID = O.OWNER# AND O.OBJ# = D.OBJ# AND D.OBJ# = DIM.IRID AND ( 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 O.OBJ# = H.DIMOBJ# AND DIM.IRID = CE.ELEMENT_IRID (+) AND CE.NAME (+) = 'DIMENSION' WITH READ ONLY