VIEW: OLAPSYS.ALL$AW_CUBE_AGG_LEVELS

Source

View Text - Preformatted

select c.owner owner,
          c.name cube_name,
          cagg.name aggregation_name,
          d.owner dimension_owner,
          d.name dimension_name,
          l.name level_name
   from
        cwm2$awcubeagg cagg,
        cwm2$awcubeagglevel caggl,
        cwm2$cube c,
        cwm2$dimension d,
        cwm2$level l
   where cagg.cube_irid = c.irid and
         cagg.version_id = 'CWM2' and
         (c.invalid = 'N' or c.invalid = 'O') and
         caggl.cubeagg_irid = cagg.irid and
         caggl.dim_irid = d.irid and
         caggl.level_irid = l.irid and
         (cwm2$security.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
  union all
   select sch.physicalname owner,
          c.physicalname cube_name,
          cagg.name aggregation_name,
          u.username dimension_owner,
          dim1.name dimension_name,
          l.name level_name
   from cwm$model sch,
        cwm$cube c,
        cwm$dimension dim1,
        dba_users u,
        sys.obj$ o,
        sys.dim$ d,
        cwm2$awcubeagg cagg,
        cwm2$awcubeagglevel caggl,
        cwm$level l
   where sch.irid = c.datamodel_irid and
         cagg.cube_irid = c.irid and
         cagg.version_id = 'CWM' and
         caggl.cubeagg_irid = cagg.irid and
         caggl.dim_irid = dim1.irid and
         caggl.level_irid = l.irid and
         d.obj# = dim1.irid and
         o.obj# = d.obj# and
         u.user_id = o.owner# and
         (cwm$util.fact_table_visible(c.irid) = 'Y'
            OR EXISTS (select null from v$enabledprivs
                       where priv_number in (-47)))
with read only

View Text - HTML Formatted

SELECT C.OWNER OWNER
, C.NAME CUBE_NAME
, CAGG.NAME AGGREGATION_NAME
, D.OWNER DIMENSION_OWNER
, D.NAME DIMENSION_NAME
, L.NAME LEVEL_NAME FROM CWM2$AWCUBEAGG CAGG
, CWM2$AWCUBEAGGLEVEL CAGGL
, CWM2$CUBE C
, CWM2$DIMENSION D
, CWM2$LEVEL L
WHERE CAGG.CUBE_IRID = C.IRID AND CAGG.VERSION_ID = 'CWM2' AND (C.INVALID = 'N' OR C.INVALID = 'O') AND CAGGL.CUBEAGG_IRID = CAGG.IRID AND CAGGL.DIM_IRID = D.IRID AND CAGGL.LEVEL_IRID = L.IRID AND (CWM2$SECURITY.FACT_TABLE_VISIBLE(C.IRID) = 'Y' OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47))) UNION ALL SELECT SCH.PHYSICALNAME OWNER
, C.PHYSICALNAME CUBE_NAME
, CAGG.NAME AGGREGATION_NAME
, U.USERNAME DIMENSION_OWNER
, DIM1.NAME DIMENSION_NAME
, L.NAME LEVEL_NAME
FROM CWM$MODEL SCH
, CWM$CUBE C
, CWM$DIMENSION DIM1
, DBA_USERS U
, SYS.OBJ$ O
, SYS.DIM$ D
, CWM2$AWCUBEAGG CAGG
, CWM2$AWCUBEAGGLEVEL CAGGL
, CWM$LEVEL L
WHERE SCH.IRID = C.DATAMODEL_IRID AND CAGG.CUBE_IRID = C.IRID AND CAGG.VERSION_ID = 'CWM' AND CAGGL.CUBEAGG_IRID = CAGG.IRID AND CAGGL.DIM_IRID = DIM1.IRID AND CAGGL.LEVEL_IRID = L.IRID AND D.OBJ# = DIM1.IRID AND O.OBJ# = D.OBJ# AND U.USER_ID = O.OWNER# AND (CWM$UTIL.FACT_TABLE_VISIBLE(C.IRID) = 'Y' OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47))) WITH READ ONLY