VIEW: SYS.ALL_CUBE_HIER_LEVELS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  h.hierarchy_name HIERARCHY_NAME,
  dl.level_name LEVEL_NAME,
  hl.hierarchy_level_id HIERARCHY_LEVEL_ID,
  hl.order_num ORDER_NUM,
  d.description_value DESCRIPTION
FROM
  olap_hier_levels$ hl,
  user$ u, obj$ o,
  olap_hierarchies$ h,
  olap_dim_levels$ dl,
  (select d.* from olap_descriptions$ d, nls_session_parameters n where
        n.parameter = 'NLS_LANGUAGE'
        and d.description_type = 'Description'
        and d.owning_object_type = 14 --HIER_LEVEL
        and (d.language = n.value
             or d.language like n.value || '\_%' escape '\')) d
WHERE
  hl.hierarchy_id=h.hierarchy_id AND o.owner#=u.user#
  AND dl.level_id=hl.dim_level_id AND o.obj#=dl.dim_obj#
  AND d.owning_object_id(+)=hl.hierarchy_level_id
  AND (o.owner# in (userenv('SCHEMAID'), 1)   -- public objects
       or o.obj# in
            ( select obj#  -- directly granted privileges
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or   -- user has system privileges
              ( exists (select null from v$enabledprivs
                        where priv_number in (-302, -- ALTER ANY PRIMARY DIMENSION
                                              -304, -- DELETE ANY PRIMARY DIMENSION
                                              -305, -- DROP ANY PRIMARY DIMENSION
                                              -306, -- INSERT ANY PRIMARY DIMENSION
                                              -307) -- SELECT ANY PRIMARY DIMENSION
                        )
              )
            )

View Text - HTML Formatted

SELECT U.NAME OWNER
, O.NAME DIMENSION_NAME
, H.HIERARCHY_NAME HIERARCHY_NAME
, DL.LEVEL_NAME LEVEL_NAME
, HL.HIERARCHY_LEVEL_ID HIERARCHY_LEVEL_ID
, HL.ORDER_NUM ORDER_NUM
, D.DESCRIPTION_VALUE DESCRIPTION FROM OLAP_HIER_LEVELS$ HL
, USER$ U
, OBJ$ O
, OLAP_HIERARCHIES$ H
, OLAP_DIM_LEVELS$ DL
, (SELECT D.*
FROM OLAP_DESCRIPTIONS$ D
, NLS_SESSION_PARAMETERS N WHERE N.PARAMETER = 'NLS_LANGUAGE'
AND D.DESCRIPTION_TYPE = 'DESCRIPTION'
AND D.OWNING_OBJECT_TYPE = 14 --HIER_LEVEL
AND (D.LANGUAGE = N.VALUE OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D WHERE HL.HIERARCHY_ID=H.HIERARCHY_ID
AND O.OWNER#=U.USER#
AND DL.LEVEL_ID=HL.DIM_LEVEL_ID
AND O.OBJ#=DL.DIM_OBJ#
AND D.OWNING_OBJECT_ID(+)=HL.HIERARCHY_LEVEL_ID
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR O.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-302
, -- ALTER ANY PRIMARY DIMENSION -304
, -- DELETE ANY PRIMARY DIMENSION -305
, -- DROP ANY PRIMARY DIMENSION -306
, -- INSERT ANY PRIMARY DIMENSION -307) -- SELECT ANY PRIMARY DIMENSION ) ) )