VIEW: OLAPSYS.ALL$OLAPMR_ATTRIBUTES

Source

View Text - Preformatted

SELECT
  u.username                 dim_owner,
  d.name                 dim_name,
  dat.PHYSICALNAME       dim_attribute_name,
  l.levelname            levelname,
  decode(c.type#, 2,
  decode(c.scale,NULL,
  decode(c.precision#, NULL, 'DOUBLE', 'FLOAT'),
         'DOUBLE'),
  8, 'DOUBLE',
  12, 'DATE',
  'STRING')              dtype,
  cu.username                col_owner,
  ct.name                col_table,
  c.name                 col_name
FROM
  dba_users              u,
  sys.obj$               d,
  sys.dimlevel$          l,
  sys.dimattr$           a,
  sys.col$               c,
  sys.obj$               ct,
  dba_users              cu,
  cwm$levelattribute     lat,
  cwm$itemuse            iu1,
  cwm$itemuse            iu2,
  cwm$dimensionattribute dat
WHERE u.user_id = d.owner# AND
      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
      d.obj# = l.dimobj# AND
      l.dimobj# = a.dimobj# AND
      l.levelid# = a.levelid# AND
      a.detailobj# = c.obj# AND
      a.col# = c.col# AND
      c.obj# = ct.obj# AND
      ct.owner# = cu.user_id AND
      c.obj# = lat.type_irid AND
      c.name = lat.physicalname AND
      lat.irid = iu2.mappable_irid AND
      iu2.operation_irid_1 = iu1.operation_irid AND
      iu1.mappable_irid = dat.irid
ORDER BY
      u.username, d.name, dat.physicalname

View Text - HTML Formatted

SELECT U.USERNAME DIM_OWNER
, D.NAME DIM_NAME
, DAT.PHYSICALNAME DIM_ATTRIBUTE_NAME
, L.LEVELNAME LEVELNAME
, DECODE(C.TYPE#
, 2
, DECODE(C.SCALE
, NULL
, DECODE(C.PRECISION#
, NULL
, 'DOUBLE'
, 'FLOAT')
, 'DOUBLE')
, 8
, 'DOUBLE'
, 12
, 'DATE'
, 'STRING') DTYPE
, CU.USERNAME COL_OWNER
, CT.NAME COL_TABLE
, C.NAME COL_NAME FROM DBA_USERS U
, SYS.OBJ$ D
, SYS.DIMLEVEL$ L
, SYS.DIMATTR$ A
, SYS.COL$ C
, SYS.OBJ$ CT
, DBA_USERS CU
, CWM$LEVELATTRIBUTE LAT
, CWM$ITEMUSE IU1
, CWM$ITEMUSE IU2
, CWM$DIMENSIONATTRIBUTE DAT WHERE U.USER_ID = D.OWNER# AND 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 D.OBJ# = L.DIMOBJ# AND L.DIMOBJ# = A.DIMOBJ# AND L.LEVELID# = A.LEVELID# AND A.DETAILOBJ# = C.OBJ# AND A.COL# = C.COL# AND C.OBJ# = CT.OBJ# AND CT.OWNER# = CU.USER_ID AND C.OBJ# = LAT.TYPE_IRID AND C.NAME = LAT.PHYSICALNAME AND LAT.IRID = IU2.MAPPABLE_IRID AND IU2.OPERATION_IRID_1 = IU1.OPERATION_IRID AND IU1.MAPPABLE_IRID = DAT.IRID ORDER BY U.USERNAME
, D.NAME
, DAT.PHYSICALNAME