VIEW: OLAPSYS.ALL$OLAPMR_DIM_LEVELS_KEYMAPS

Source

View Text - Preformatted

SELECT /*+ORDERED*/
  u.username                             owner,
  d.name                             dimension_name,
  h.hiername                         hierarchy_name,
  dl.levelname                       level_name,
  NVL(lev.displayname,dl.levelname)  display_name,
  NVL(lev.description,dl.levelname)  short_description,
  NVL(lev.description,dl.levelname)  description,
  hl.pos#                            pos,
  tu.username table_owner,
  t.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,
  k.keypos# column_position
FROM
  sys.obj$          d,
  dba_users         u,
  sys.hier$         h,
  sys.hierlevel$    hl,
  sys.dimlevel$     dl,
  sys.dimlevelkey$  k,
  sys.obj$          t,
  dba_users         tu,
  cwm$level         lev,
  sys.col$          c
WHERE
  d.type#      = 43 AND              /* DIMENSION */
 (   cwm$util.dimension_tables_visible(d.obj#) = 'Y'
     OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */
       (SELECT null FROM v$enabledprivs
        WHERE priv_number IN (-47,-215,-216,-217)))
AND
  u.user_id      = d.owner# AND
  d.obj#       = h.dimobj# AND
  d.obj#       = hl.dimobj# AND
  h.hierid#    = hl.hierid# AND
  hl.dimobj#   = dl.dimobj# AND
  hl.levelid#  = dl.levelid# AND
  dl.dimobj#   = k.dimobj# AND
  dl.levelid#  = k.levelid# AND
  k.detailobj# = t.obj# AND
  t.owner#     = tu.user_id AND
  dl.dimobj#   = lev.dimension_irid AND
  dl.levelname = lev.physicalname (+)
AND k.detailobj# = c.obj#
AND k.col# = c.col#

View Text - HTML Formatted

SELECT /*+ORDERED*/ U.USERNAME OWNER
, D.NAME DIMENSION_NAME
, H.HIERNAME HIERARCHY_NAME
, DL.LEVELNAME LEVEL_NAME
, NVL(LEV.DISPLAYNAME
, DL.LEVELNAME) DISPLAY_NAME
, NVL(LEV.DESCRIPTION
, DL.LEVELNAME) SHORT_DESCRIPTION
, NVL(LEV.DESCRIPTION
, DL.LEVELNAME) DESCRIPTION
, HL.POS# POS
, TU.USERNAME TABLE_OWNER
, T.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
, K.KEYPOS# COLUMN_POSITION FROM SYS.OBJ$ D
, DBA_USERS U
, SYS.HIER$ H
, SYS.HIERLEVEL$ HL
, SYS.DIMLEVEL$ DL
, SYS.DIMLEVELKEY$ K
, SYS.OBJ$ T
, DBA_USERS TU
, CWM$LEVEL LEV
, SYS.COL$ C WHERE D.TYPE# = 43
AND /* DIMENSION */ ( CWM$UTIL.DIMENSION_TABLES_VISIBLE(D.OBJ#) = 'Y' OR EXISTS /* SELECT ANY TABLE
, CREATE
, ALTER
, DROP ANY DIMENSION */ (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217))) AND U.USER_ID = D.OWNER# AND D.OBJ# = H.DIMOBJ# AND D.OBJ# = HL.DIMOBJ# AND H.HIERID# = HL.HIERID# AND HL.DIMOBJ# = DL.DIMOBJ# AND HL.LEVELID# = DL.LEVELID# AND DL.DIMOBJ# = K.DIMOBJ# AND DL.LEVELID# = K.LEVELID# AND K.DETAILOBJ# = T.OBJ# AND T.OWNER# = TU.USER_ID AND DL.DIMOBJ# = LEV.DIMENSION_IRID AND DL.LEVELNAME = LEV.PHYSICALNAME (+) AND K.DETAILOBJ# = C.OBJ# AND K.COL# = C.COL#