eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.ALL_CUBES

Source

View Text - Preformatted


SELECT
  u.name OWNER,
  o.name CUBE_NAME,
  c.obj# CUBE_ID,
  a.awname AW_NAME,
  syn.syntax_clob CONSISTENT_SOLVE_SPEC,
  d.description_value DESCRIPTION,
  io.option_value SPARSE_TYPE,
  syn2.syntax_clob PRECOMPUTE_CONDITION,
  io2.option_num_value PRECOMPUTE_PERCENT,
  io3.option_num_value PRECOMPUTE_PERCENT_TOP,
  od.name PARTITION_DIMENSION_NAME,
  h.hierarchy_name PARTITION_HIERARCHY_NAME,
  dl.level_name PARTITION_LEVEL_NAME,
  io5.option_value REFRESH_MVIEW_NAME,
  io6.option_value REWRITE_MVIEW_NAME,
  syn3.syntax_clob DEFAULT_BUILD_SPEC,
  io7.option_value MEASURE_STORAGE,
  syn4.syntax_clob SQL_CUBE_STORAGE_TYPE,
  io8.option_value CUBE_STORAGE_TYPE
FROM
  olap_cubes$ c,
  user$ u,
  aw$ a,
  obj$ o,
  olap_syntax$ syn,
  olap_syntax$ syn2,
  olap_syntax$ syn3,
  olap_syntax$ syn4,
  (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 = 1 --CUBE
        and (d.language = n.value
             or d.language like n.value || '\_%' escape '\')) d,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  olap_impl_options$ io4,
  olap_impl_options$ io5,
  olap_impl_options$ io6,
  olap_impl_options$ io7,
  olap_impl_options$ io8,
  olap_hier_levels$ hl,
  olap_dim_levels$ dl,
  olap_hierarchies$ h,
  obj$ od,
  (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
  o.obj#=c.obj#
  AND c.obj#=da.obj#(+)
  AND o.owner#=u.user#(+)
  AND c.awseq#=a.awseq#(+)
  AND c.obj#=d.owning_object_id(+)
  AND syn.owner_id(+)=c.obj#
  AND syn.owner_type(+)=1
  AND syn.ref_role(+)=16 -- consistent solve spec
  AND syn2.owner_id(+)=c.obj#
  AND syn2.owner_type(+)=1
  AND syn2.ref_role(+)=20 -- precompute condition
  AND syn3.owner_id(+)=c.obj#
  AND syn3.owner_type(+)=1
  AND syn3.ref_role(+)=17 -- default build spec
  AND syn4.owner_id(+)=c.obj#
  AND syn4.owner_type(+)=1
  AND syn4.ref_role(+)=24 -- sql cube storage type
  AND io.owning_objectid(+)=c.obj#
  AND io.object_type(+)=1
  AND io.option_type(+)=7 -- sparse type
  AND io2.owning_objectid(+)=c.obj#
  AND io2.object_type(+)=1
  AND io2.option_type(+)=24 -- precompute percent
  AND io3.owning_objectid(+)=c.obj#
  AND io3.object_type(+)=1
  AND io3.option_type(+)=25 -- precompute percent top
  AND io4.owning_objectid(+)=c.obj#
  AND io4.object_type(+)=1
  AND io4.option_type(+)=9 -- partition level
  AND io4.option_num_value=hl.hierarchy_level_id(+)
  AND io5.owning_objectid(+)=c.obj#
  AND io5.object_type(+)=1
  AND io5.option_type(+)=30 -- refresh MV name
  AND io6.owning_objectid(+)=c.obj#
  AND io6.object_type(+)=1
  AND io6.option_type(+)=31 -- rewrite MV name
  AND io7.owning_objectid(+)=c.obj#
  AND io7.object_type(+)=1
  AND io7.option_type(+)=17 -- measure storage
  AND io8.owning_objectid(+)=c.obj#
  AND io8.object_type(+)=1
  AND io8.option_type(+)=20 -- cube storage type
  AND hl.hierarchy_id=h.hierarchy_id(+)
  AND hl.dim_level_id=dl.level_id(+)
  AND h.dim_obj#=od.obj#(+)
  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
, C.OBJ# CUBE_ID
, A.AWNAME AW_NAME
, SYN.SYNTAX_CLOB CONSISTENT_SOLVE_SPEC
, D.DESCRIPTION_VALUE DESCRIPTION
, IO.OPTION_VALUE SPARSE_TYPE
, SYN2.SYNTAX_CLOB PRECOMPUTE_CONDITION
, IO2.OPTION_NUM_VALUE PRECOMPUTE_PERCENT
, IO3.OPTION_NUM_VALUE PRECOMPUTE_PERCENT_TOP
, OD.NAME PARTITION_DIMENSION_NAME
, H.HIERARCHY_NAME PARTITION_HIERARCHY_NAME
, DL.LEVEL_NAME PARTITION_LEVEL_NAME
, IO5.OPTION_VALUE REFRESH_MVIEW_NAME
, IO6.OPTION_VALUE REWRITE_MVIEW_NAME
, SYN3.SYNTAX_CLOB DEFAULT_BUILD_SPEC
, IO7.OPTION_VALUE MEASURE_STORAGE
, SYN4.SYNTAX_CLOB SQL_CUBE_STORAGE_TYPE
, IO8.OPTION_VALUE CUBE_STORAGE_TYPE FROM OLAP_CUBES$ C
, USER$ U
, AW$ A
, OBJ$ O
, OLAP_SYNTAX$ SYN
, OLAP_SYNTAX$ SYN2
, OLAP_SYNTAX$ SYN3
, OLAP_SYNTAX$ SYN4
, (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 = 1 --CUBE
AND (D.LANGUAGE = N.VALUE OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, OLAP_IMPL_OPTIONS$ IO4
, OLAP_IMPL_OPTIONS$ IO5
, OLAP_IMPL_OPTIONS$ IO6
, OLAP_IMPL_OPTIONS$ IO7
, OLAP_IMPL_OPTIONS$ IO8
, OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ OD
, (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 O.OBJ#=C.OBJ#
AND C.OBJ#=DA.OBJ#(+)
AND O.OWNER#=U.USER#(+)
AND C.AWSEQ#=A.AWSEQ#(+)
AND C.OBJ#=D.OWNING_OBJECT_ID(+)
AND SYN.OWNER_ID(+)=C.OBJ#
AND SYN.OWNER_TYPE(+)=1
AND SYN.REF_ROLE(+)=16 -- CONSISTENT SOLVE SPEC
AND SYN2.OWNER_ID(+)=C.OBJ#
AND SYN2.OWNER_TYPE(+)=1
AND SYN2.REF_ROLE(+)=20 -- PRECOMPUTE CONDITION
AND SYN3.OWNER_ID(+)=C.OBJ#
AND SYN3.OWNER_TYPE(+)=1
AND SYN3.REF_ROLE(+)=17 -- DEFAULT BUILD SPEC
AND SYN4.OWNER_ID(+)=C.OBJ#
AND SYN4.OWNER_TYPE(+)=1
AND SYN4.REF_ROLE(+)=24 -- SQL CUBE STORAGE TYPE
AND IO.OWNING_OBJECTID(+)=C.OBJ#
AND IO.OBJECT_TYPE(+)=1
AND IO.OPTION_TYPE(+)=7 -- SPARSE TYPE
AND IO2.OWNING_OBJECTID(+)=C.OBJ#
AND IO2.OBJECT_TYPE(+)=1
AND IO2.OPTION_TYPE(+)=24 -- PRECOMPUTE PERCENT
AND IO3.OWNING_OBJECTID(+)=C.OBJ#
AND IO3.OBJECT_TYPE(+)=1
AND IO3.OPTION_TYPE(+)=25 -- PRECOMPUTE PERCENT TOP
AND IO4.OWNING_OBJECTID(+)=C.OBJ#
AND IO4.OBJECT_TYPE(+)=1
AND IO4.OPTION_TYPE(+)=9 -- PARTITION LEVEL
AND IO4.OPTION_NUM_VALUE=HL.HIERARCHY_LEVEL_ID(+)
AND IO5.OWNING_OBJECTID(+)=C.OBJ#
AND IO5.OBJECT_TYPE(+)=1
AND IO5.OPTION_TYPE(+)=30 -- REFRESH MV NAME
AND IO6.OWNING_OBJECTID(+)=C.OBJ#
AND IO6.OBJECT_TYPE(+)=1
AND IO6.OPTION_TYPE(+)=31 -- REWRITE MV NAME
AND IO7.OWNING_OBJECTID(+)=C.OBJ#
AND IO7.OBJECT_TYPE(+)=1
AND IO7.OPTION_TYPE(+)=17 -- MEASURE STORAGE
AND IO8.OWNING_OBJECTID(+)=C.OBJ#
AND IO8.OBJECT_TYPE(+)=1
AND IO8.OPTION_TYPE(+)=20 -- CUBE STORAGE TYPE
AND HL.HIERARCHY_ID=H.HIERARCHY_ID(+)
AND HL.DIM_LEVEL_ID=DL.LEVEL_ID(+)
AND H.DIM_OBJ#=OD.OBJ#(+)
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))