VIEW: OLAPSYS.ODM$OLAP2ULEVEL_KEY_COL_USES

Source

View Text - Preformatted

select distinct lkcu.owner owner,
       lkcu.dimension_name dimension_name,
       nvl(dhl.hierarchy_name ,'NONE') hierarchy_name,
       lkcu.level_name child_level_name,
       lkcu.table_owner table_owner,
       lkcu.table_name table_name,
       lkcu.column_name column_name,
       lkcu.position position
from
    (select u.username owner,
            d.name dimension_name,
            l.levelname level_name,
            c.name column_name,
            k.keypos# position,
            tu.username table_owner,
            tn.name table_name
      from dba_users u,
           sys.obj$ d,
           sys.dimlevel$ l,
           sys.dimlevelkey$ k,
           sys.col$ c,
           sys.obj$ tn,
           dba_users tu,
           olapsys.cwm$level lev
      where u.user_id = d.owner# and
            d.type# = 43 and
            (cwm$util.dimension_tables_visible(d.obj#) = 'Y'
             OR EXISTS
                (select null from v$enabledprivs
                 where priv_number in (-47, -215, -216, -217))) and
            d.obj# = l.dimobj# and
            l.dimobj# = k.dimobj# and
            l.levelid# = k.levelid# and
            k.detailobj# = c.obj# and
            k.col# = c.col# and
            tn.obj# = c.obj# and
            tu.user_id = tn.owner# and
            lev.dimension_irid = l.dimobj# and
            lev.physicalname = l.levelname
      ) lkcu,
    (select u.username  dim_owner,
           o.name  dim_name,
           h.hiername  hierarchy_name,
           dl.levelname level_name
      from dba_users u,
           sys.obj$ o,
           sys.dim$ d,
           sys.dimlevel$ dl,
           sys.hier$ h,
           sys.hierlevel$ hl,
           olapsys.cwm$level lev
      where u.user_id = o.owner# and
            o.type# = 43 and
            o.obj# = d.obj# and
            d.obj# = dl.dimobj# and
            d.obj# = h.dimobj# and
            h.dimobj# = hl.dimobj# and
            h.hierid# = hl.hierid# and
            hl.levelid# = dl.levelid# and
            lev.dimension_irid = dl.dimobj# and
            lev.physicalname = dl.levelname
            ) dhl
    where  lkcu.owner = dhl.dim_owner (+) and
           lkcu.dimension_name = dhl.dim_name (+) and
           lkcu.level_name = dhl.level_name (+)
UNION ALL
select owner, dimension_name, hierarchy_name, child_level_name,
       table_owner, table_name, column_name, position
from olapsys.ODM$olap2_level_key_col_uses
with read only

View Text - HTML Formatted

SELECT DISTINCT LKCU.OWNER OWNER
, LKCU.DIMENSION_NAME DIMENSION_NAME
, NVL(DHL.HIERARCHY_NAME
, 'NONE') HIERARCHY_NAME
, LKCU.LEVEL_NAME CHILD_LEVEL_NAME
, LKCU.TABLE_OWNER TABLE_OWNER
, LKCU.TABLE_NAME TABLE_NAME
, LKCU.COLUMN_NAME COLUMN_NAME
, LKCU.POSITION POSITION FROM (SELECT U.USERNAME OWNER
, D.NAME DIMENSION_NAME
, L.LEVELNAME LEVEL_NAME
, C.NAME COLUMN_NAME
, K.KEYPOS# POSITION
, TU.USERNAME TABLE_OWNER
, TN.NAME TABLE_NAME
FROM DBA_USERS U
, SYS.OBJ$ D
, SYS.DIMLEVEL$ L
, SYS.DIMLEVELKEY$ K
, SYS.COL$ C
, SYS.OBJ$ TN
, DBA_USERS TU
, OLAPSYS.CWM$LEVEL LEV
WHERE U.USER_ID = D.OWNER# AND D.TYPE# = 43 AND (CWM$UTIL.DIMENSION_TABLES_VISIBLE(D.OBJ#) = 'Y' OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217))) AND D.OBJ# = L.DIMOBJ# AND L.DIMOBJ# = K.DIMOBJ# AND L.LEVELID# = K.LEVELID# AND K.DETAILOBJ# = C.OBJ# AND K.COL# = C.COL# AND TN.OBJ# = C.OBJ# AND TU.USER_ID = TN.OWNER# AND LEV.DIMENSION_IRID = L.DIMOBJ# AND LEV.PHYSICALNAME = L.LEVELNAME ) LKCU
, (SELECT U.USERNAME DIM_OWNER
, O.NAME DIM_NAME
, H.HIERNAME HIERARCHY_NAME
, DL.LEVELNAME LEVEL_NAME
FROM DBA_USERS U
, SYS.OBJ$ O
, SYS.DIM$ D
, SYS.DIMLEVEL$ DL
, SYS.HIER$ H
, SYS.HIERLEVEL$ HL
, OLAPSYS.CWM$LEVEL LEV
WHERE U.USER_ID = O.OWNER# AND O.TYPE# = 43 AND O.OBJ# = D.OBJ# AND D.OBJ# = DL.DIMOBJ# AND D.OBJ# = H.DIMOBJ# AND H.DIMOBJ# = HL.DIMOBJ# AND H.HIERID# = HL.HIERID# AND HL.LEVELID# = DL.LEVELID# AND LEV.DIMENSION_IRID = DL.DIMOBJ# AND LEV.PHYSICALNAME = DL.LEVELNAME ) DHL
WHERE LKCU.OWNER = DHL.DIM_OWNER (+) AND LKCU.DIMENSION_NAME = DHL.DIM_NAME (+) AND LKCU.LEVEL_NAME = DHL.LEVEL_NAME (+) UNION ALL SELECT OWNER
, DIMENSION_NAME
, HIERARCHY_NAME
, CHILD_LEVEL_NAME
, TABLE_OWNER
, TABLE_NAME
, COLUMN_NAME
, POSITION FROM OLAPSYS.ODM$OLAP2_LEVEL_KEY_COL_USES WITH READ ONLY