VIEW: SYS.ALL_CUBE_CALCULATED_MEMBERS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  o.name DIMENSION_NAME,
  cm.member_name MEMBER_NAME,
  DECODE(cm.is_customaggregate, 1, 'YES', 0, 'NO') IS_CUSTOM_AGGREGATE,
  DECODE(cm.storage_type, 1, 'DYNAMIC', 2, 'PRECOMPUTE') STORAGE_TYPE,
  syn.syntax_clob EXPRESSION
FROM
  olap_calculated_members$ cm,
  obj$ o,
  user$ u,
  olap_syntax$ syn
WHERE
  cm.dim_obj#=o.obj#
  AND o.owner#=u.user#
  AND cm.member_id = syn.owner_id(+)
  AND syn.owner_type = 6 --CALC_MEMBER
  AND syn.ref_role=19 -- MEMBER_EXPRESSION_ROLE
  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
, CM.MEMBER_NAME MEMBER_NAME
, DECODE(CM.IS_CUSTOMAGGREGATE
, 1
, 'YES'
, 0
, 'NO') IS_CUSTOM_AGGREGATE
, DECODE(CM.STORAGE_TYPE
, 1
, 'DYNAMIC'
, 2
, 'PRECOMPUTE') STORAGE_TYPE
, SYN.SYNTAX_CLOB EXPRESSION FROM OLAP_CALCULATED_MEMBERS$ CM
, OBJ$ O
, USER$ U
, OLAP_SYNTAX$ SYN WHERE CM.DIM_OBJ#=O.OBJ#
AND O.OWNER#=U.USER#
AND CM.MEMBER_ID = SYN.OWNER_ID(+)
AND SYN.OWNER_TYPE = 6 --CALC_MEMBER
AND SYN.REF_ROLE=19 -- MEMBER_EXPRESSION_ROLE
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 ) ) )