VIEW: SYS.ALL_CUBE_MEASURES

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  o.name CUBE_NAME,
  m.measure_name MEASURE_NAME,
  m.measure_id MEASURE_ID,
  ss.syntax_clob OVERRIDE_SOLVE_SPEC,
  DECODE(m.measure_type, 1, 'BASE', 2, 'DERIVED') MEASURE_TYPE,
  DECODE(m.measure_type, 2, s.syntax_clob) EXPRESSION,
  d.description_value DESCRIPTION,
  DECODE(m.type#, 1, decode(m.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                  2, decode(m.scale, null,
                            decode(m.precision#, null, 'NUMBER', 'FLOAT'),
                            'NUMBER'),
                  8, 'LONG',
                  9, decode(m.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                  12, 'DATE',
                  23, 'RAW', 24, 'LONG RAW',
                  69, 'ROWID',
                  96, decode(m.charsetform, 2, 'NCHAR', 'CHAR'),
                  100, 'BINARY_FLOAT',
                  101, 'BINARY_DOUBLE',
                  105, 'MLSLABEL',
                  106, 'MLSLABEL',
                  112, decode(m.charsetform, 2, 'NCLOB', 'CLOB'),
                  113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                  178, 'TIME(' ||m.scale|| ')',
                  179, 'TIME(' ||m.scale|| ')' || ' WITH TIME ZONE',
                  180, 'TIMESTAMP(' ||m.scale|| ')',
                  181, 'TIMESTAMP(' ||m.scale|| ')' || ' WITH TIME ZONE',
                  231, 'TIMESTAMP(' ||m.scale|| ')' || ' WITH LOCAL TIME ZONE',
                  182, 'INTERVAL YEAR(' ||m.precision#||') TO MONTH',
                  183, 'INTERVAL DAY(' ||m.precision#||') TO SECOND(' ||
                        m.scale || ')',
                  208, 'UROWID',
                  'UNDEFINED') DATA_TYPE,
  m.length DATA_LENGTH,
  m.precision# DATA_PRECISION,
  m.scale DATA_SCALE,
  io.option_value LOOP_VAR_OVERRIDE,
  io2.option_value LOOP_DENSE_OVERRIDE,
  io3.option_value LOOP_TYPE
FROM
  olap_measures$ m,
  user$ u,
  obj$ o,
  olap_syntax$ ss,
  olap_syntax$ s,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  (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 = 2 --MEASURE
        and (d.language = n.value
             or d.language like n.value || '\_%' escape '\')) d,
 (SELECT
    obj#,
    MIN(have_dim_access) have_all_dim_access
  FROM
    (SELECT
      c.obj# obj#,
      (CASE
        WHEN
        (do.owner# in (userenv('SCHEMAID'), 1)   -- public objects
         or do.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
                          )
                )
        )
        THEN 1
        ELSE 0
       END) have_dim_access
    FROM
      olap_cubes$ c,
      dependency$ d,
      obj$ do
    WHERE
      do.obj# = d.p_obj#
      AND do.type# = 92 -- CUBE DIMENSION
      AND c.obj# = d.d_obj#
    )
    GROUP BY obj# ) da
WHERE
  m.cube_obj#=o.obj#
  AND o.obj#=da.obj#(+)
  AND o.owner#=u.user#
  AND m.measure_id=s.owner_id(+)
  AND m.measure_id=ss.owner_id(+)
  AND m.is_hidden=0 --NOT HIDDEN
  AND s.owner_type(+)=2 --MEASURE
  AND s.ref_role(+)=14 --DERIVED_MEAS_EXPRESSION
  AND ss.owner_type(+)=2 --MEASURE
  AND ss.ref_role(+)=16 --CONSISTENT_SOLVE_SPEC
  AND m.measure_id=d.owning_object_id(+)
  AND io.owning_objectid(+)=m.measure_id
  AND io.object_type(+)=2
  AND io.option_type(+)=51 -- LOOP_VAR_OVERRIDE
  AND io2.owning_objectid(+)=m.measure_id
  AND io2.object_type(+)=2
  AND io2.option_type(+)=52 -- LOOP_DENSE_OVERRIDE
  AND io3.owning_objectid(+)=m.measure_id
  AND io3.object_type(+)=2
  AND io3.option_type(+)=53 -- LOOP_TYPE
  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 (-309, -- ALTER ANY CUBE
                                              -311, -- DROP ANY CUBE
                                              -312, -- SELECT ANY CUBE
                                              -313) -- UPDATE ANY CUBE
                        )
              )
            )
  AND ((have_all_dim_access = 1) OR (have_all_dim_access is NULL))

View Text - HTML Formatted

SELECT U.NAME OWNER
, O.NAME CUBE_NAME
, M.MEASURE_NAME MEASURE_NAME
, M.MEASURE_ID MEASURE_ID
, SS.SYNTAX_CLOB OVERRIDE_SOLVE_SPEC
, DECODE(M.MEASURE_TYPE
, 1
, 'BASE'
, 2
, 'DERIVED') MEASURE_TYPE
, DECODE(M.MEASURE_TYPE
, 2
, S.SYNTAX_CLOB) EXPRESSION
, D.DESCRIPTION_VALUE DESCRIPTION
, DECODE(M.TYPE#
, 1
, DECODE(M.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(M.SCALE
, NULL
, DECODE(M.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(M.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(M.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 100
, 'BINARY_FLOAT'
, 101
, 'BINARY_DOUBLE'
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(M.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||M.SCALE|| ')'
, 179
, 'TIME(' ||M.SCALE|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||M.SCALE|| ')'
, 181
, 'TIMESTAMP(' ||M.SCALE|| ')' || ' WITH TIME ZONE'
, 231
, 'TIMESTAMP(' ||M.SCALE|| ')' || ' WITH LOCAL TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||M.PRECISION#||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||M.PRECISION#||') TO SECOND(' || M.SCALE || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, M.LENGTH DATA_LENGTH
, M.PRECISION# DATA_PRECISION
, M.SCALE DATA_SCALE
, IO.OPTION_VALUE LOOP_VAR_OVERRIDE
, IO2.OPTION_VALUE LOOP_DENSE_OVERRIDE
, IO3.OPTION_VALUE LOOP_TYPE FROM OLAP_MEASURES$ M
, USER$ U
, OBJ$ O
, OLAP_SYNTAX$ SS
, OLAP_SYNTAX$ S
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, (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 = 2 --MEASURE
AND (D.LANGUAGE = N.VALUE OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D
, (SELECT OBJ#
, MIN(HAVE_DIM_ACCESS) HAVE_ALL_DIM_ACCESS FROM (SELECT C.OBJ# OBJ#
, (CASE WHEN (DO.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR DO.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 ) ) ) THEN 1 ELSE 0 END) HAVE_DIM_ACCESS FROM OLAP_CUBES$ C
, DEPENDENCY$ D
, OBJ$ DO WHERE DO.OBJ# = D.P_OBJ#
AND DO.TYPE# = 92 -- CUBE DIMENSION
AND C.OBJ# = D.D_OBJ# ) GROUP BY OBJ# ) DA WHERE M.CUBE_OBJ#=O.OBJ#
AND O.OBJ#=DA.OBJ#(+)
AND O.OWNER#=U.USER#
AND M.MEASURE_ID=S.OWNER_ID(+)
AND M.MEASURE_ID=SS.OWNER_ID(+)
AND M.IS_HIDDEN=0 --NOT HIDDEN
AND S.OWNER_TYPE(+)=2 --MEASURE
AND S.REF_ROLE(+)=14 --DERIVED_MEAS_EXPRESSION
AND SS.OWNER_TYPE(+)=2 --MEASURE
AND SS.REF_ROLE(+)=16 --CONSISTENT_SOLVE_SPEC
AND M.MEASURE_ID=D.OWNING_OBJECT_ID(+)
AND IO.OWNING_OBJECTID(+)=M.MEASURE_ID
AND IO.OBJECT_TYPE(+)=2
AND IO.OPTION_TYPE(+)=51 -- LOOP_VAR_OVERRIDE
AND IO2.OWNING_OBJECTID(+)=M.MEASURE_ID
AND IO2.OBJECT_TYPE(+)=2
AND IO2.OPTION_TYPE(+)=52 -- LOOP_DENSE_OVERRIDE
AND IO3.OWNING_OBJECTID(+)=M.MEASURE_ID
AND IO3.OBJECT_TYPE(+)=2
AND IO3.OPTION_TYPE(+)=53 -- LOOP_TYPE
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 (-309
, -- ALTER ANY CUBE -311
, -- DROP ANY CUBE -312
, -- SELECT ANY CUBE -313) -- UPDATE ANY CUBE ) ) )
AND ((HAVE_ALL_DIM_ACCESS = 1) OR (HAVE_ALL_DIM_ACCESS IS NULL))