VIEW: OLAPSYS.ALL$OLAP2_DIM_LEVELS_KEYMAPS

Source

View Text - Preformatted

select
       d.owner owner,
       d.name dimension_name,
  (case when h.hidden = 'N'
        then h.name else null end) hierarchy_name,
       l.name level_name,
       NVL(L.DISPLAYNAME, L.NAME) display_name,
       NVL(L.SHORTDESCRIPTION, L.NAME) short_description,
       NVL(L.DESCRIPTION, L.NAME) description,
       hlr.leveldepth pos,
       u.username table_owner,
       o.name table_name,
       c.name column_name,
 decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(c.scale, null,
                           decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||c.spare1|| ')',
                 179, 'TIME(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||c.spare1|| ')',
                 181, 'TIMESTAMP(' ||c.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||c.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||c.spare2||') TO SECOND(' ||
                       c.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') data_type
, decode(c.length, null, 0, c.length) data_length
, decode(c.precision#, null, 0, c.precision#) data_precision,
       dhlm.position column_position
from olapsys.cwm2$dimension d,
     olapsys.cwm2$hierarchy h,
     olapsys.cwm2$level l,
     olapsys.cwm2$hierlevelrel hlr,
     olapsys.cwm2$dimhierlvlmap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ c
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.dimhierlvl_irid = hlr.irid and
      dhlm.object_id = o.obj# and
      dhlm.column_id = c.col# and
      o.obj# = c.obj# and
      o.owner# = u.user_id and
       d.invalid = 'N' and
       h.hidden = 'N' and
       (cwm2$security.dimension_tables_visible(d.irid) = 'Y'
       OR EXISTS (select null from v$enabledprivs
                  where priv_number in (-47, -215, -216, -217)))
with read only

View Text - HTML Formatted

SELECT D.OWNER OWNER
, D.NAME DIMENSION_NAME
, (CASE WHEN H.HIDDEN = 'N' THEN H.NAME ELSE NULL END) HIERARCHY_NAME
, L.NAME LEVEL_NAME
, NVL(L.DISPLAYNAME
, L.NAME) DISPLAY_NAME
, NVL(L.SHORTDESCRIPTION
, L.NAME) SHORT_DESCRIPTION
, NVL(L.DESCRIPTION
, L.NAME) DESCRIPTION
, HLR.LEVELDEPTH POS
, U.USERNAME TABLE_OWNER
, O.NAME TABLE_NAME
, C.NAME COLUMN_NAME
, DECODE(C.TYPE#
, 1
, DECODE(C.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(C.SCALE
, NULL
, DECODE(C.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(C.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(C.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||C.SPARE1|| ')'
, 179
, 'TIME(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||C.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||C.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||C.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||C.SPARE2||') TO SECOND(' || C.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, DECODE(C.LENGTH
, NULL
, 0
, C.LENGTH) DATA_LENGTH
, DECODE(C.PRECISION#
, NULL
, 0
, C.PRECISION#) DATA_PRECISION
, DHLM.POSITION COLUMN_POSITION FROM OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$HIERARCHY H
, OLAPSYS.CWM2$LEVEL L
, OLAPSYS.CWM2$HIERLEVELREL HLR
, OLAPSYS.CWM2$DIMHIERLVLMAP DHLM
, DBA_USERS U
, SYS.OBJ$ O
, SYS.COL$ C WHERE H.DIMENSION_IRID = D.IRID AND H.IRID = HLR.HIERARCHY_IRID AND L.IRID = HLR.CHILDLEVEL_IRID AND DHLM.DIMHIERLVL_IRID = HLR.IRID AND DHLM.OBJECT_ID = O.OBJ# AND DHLM.COLUMN_ID = C.COL# AND O.OBJ# = C.OBJ# AND O.OWNER# = U.USER_ID AND D.INVALID = 'N' AND H.HIDDEN = 'N' AND (CWM2$SECURITY.DIMENSION_TABLES_VISIBLE(D.IRID) = 'Y' OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217))) WITH READ ONLY