VIEW: OLAPSYS.DBA$OLAP2ULISTDIMS

Source

View Text - Preformatted

select owner,
       dimension_name,
       nvl(plural_name, dimension_name) plural_name,
       nvl(display_name, dimension_name) display_name,
       nvl(short_description, dimension_name) short_description,
       nvl(description, dimension_name) description,
       descriptor_value,
       table_owner table_owner,
       table_name table_name,
       column_name column_name,
       data_type data_type,
       data_length data_length,
       data_precision data_precision,
       position column_position
from (
    select u.username owner,
            d.name dimension_name,
 dim.pluralname plural_name,
 dim.displayname display_name,
 dim.description short_description,
 dim.description description,
 decode(o.status, 5, 'Y', 'N') invalid,
            tu.username table_owner,
            tn.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# position,
  (case when ce.classification_irid = 28 then 'Time'
       else 'Other'
       end)    descriptor_value
      from dba_users u,
           sys.obj$ d,
           sys.dimlevel$ l,
           sys.dimlevelkey$ k,
           sys.col$ c,
           sys.obj$ tn,
           dba_users tu,
           cwm$dimension dim,
           sys.obj$ o,
           sys.dim$ sd,
  olapsys.cwm$classificationentry ce
      where u.user_id = d.owner# and
            d.type# = 43 and
            o.obj# = sd.obj# and
            sd.obj# = dim.irid and
            d.obj# = dim.irid 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
            (not exists (select * from cwm$hierarchy h
            where dim.irid = h.dimension_irid))
AND dim.irid = ce.element_irid (+) and
    ce.name (+) = 'DIMENSION'
UNION ALL
select
 d.owner owner,
       d.name dimension_name,
 d.pluralname plural_name,
 d.displayname display_name,
 d.shortdescription short_description,
 d.description description,
 d.invalid invalid,
       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 position,
  (case when ce.classification_irid = 28 then 'Time'
       else 'Other'
       end)    descriptor_value
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,
  olapsys.cwm$classificationentry ce
where h.dimension_irid = d.irid and
      (h.hidden = 'Y' or h.hidden is null) 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.irid = ce.element_irid (+) and
      ce.name (+) = 'DIMENSION2'
)
with read only

View Text - HTML Formatted

SELECT OWNER
, DIMENSION_NAME
, NVL(PLURAL_NAME
, DIMENSION_NAME) PLURAL_NAME
, NVL(DISPLAY_NAME
, DIMENSION_NAME) DISPLAY_NAME
, NVL(SHORT_DESCRIPTION
, DIMENSION_NAME) SHORT_DESCRIPTION
, NVL(DESCRIPTION
, DIMENSION_NAME) DESCRIPTION
, DESCRIPTOR_VALUE
, TABLE_OWNER TABLE_OWNER
, TABLE_NAME TABLE_NAME
, COLUMN_NAME COLUMN_NAME
, DATA_TYPE DATA_TYPE
, DATA_LENGTH DATA_LENGTH
, DATA_PRECISION DATA_PRECISION
, POSITION COLUMN_POSITION FROM ( SELECT U.USERNAME OWNER
, D.NAME DIMENSION_NAME
, DIM.PLURALNAME PLURAL_NAME
, DIM.DISPLAYNAME DISPLAY_NAME
, DIM.DESCRIPTION SHORT_DESCRIPTION
, DIM.DESCRIPTION DESCRIPTION
, DECODE(O.STATUS
, 5
, 'Y'
, 'N') INVALID
, TU.USERNAME TABLE_OWNER
, TN.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# POSITION
, (CASE WHEN CE.CLASSIFICATION_IRID = 28 THEN 'TIME' ELSE 'OTHER' END) DESCRIPTOR_VALUE
FROM DBA_USERS U
, SYS.OBJ$ D
, SYS.DIMLEVEL$ L
, SYS.DIMLEVELKEY$ K
, SYS.COL$ C
, SYS.OBJ$ TN
, DBA_USERS TU
, CWM$DIMENSION DIM
, SYS.OBJ$ O
, SYS.DIM$ SD
, OLAPSYS.CWM$CLASSIFICATIONENTRY CE
WHERE U.USER_ID = D.OWNER# AND D.TYPE# = 43 AND O.OBJ# = SD.OBJ# AND SD.OBJ# = DIM.IRID AND D.OBJ# = DIM.IRID 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 (NOT EXISTS (SELECT *
FROM CWM$HIERARCHY H
WHERE DIM.IRID = H.DIMENSION_IRID)) AND DIM.IRID = CE.ELEMENT_IRID (+) AND CE.NAME (+) = 'DIMENSION' UNION ALL SELECT D.OWNER OWNER
, D.NAME DIMENSION_NAME
, D.PLURALNAME PLURAL_NAME
, D.DISPLAYNAME DISPLAY_NAME
, D.SHORTDESCRIPTION SHORT_DESCRIPTION
, D.DESCRIPTION DESCRIPTION
, D.INVALID INVALID
, 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 POSITION
, (CASE WHEN CE.CLASSIFICATION_IRID = 28 THEN 'TIME' ELSE 'OTHER' END) DESCRIPTOR_VALUE 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
, OLAPSYS.CWM$CLASSIFICATIONENTRY CE WHERE H.DIMENSION_IRID = D.IRID AND (H.HIDDEN = 'Y' OR H.HIDDEN IS NULL) 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.IRID = CE.ELEMENT_IRID (+) AND CE.NAME (+) = 'DIMENSION2' ) WITH READ ONLY