VIEW: OLAPSYS.DBA$OLAP2_FACT_TABLE_GID

Source

View Text - Preformatted

select distinct
  c.owner owner,
  c.name cube_name,
  d.owner dimension_owner,
  d.name dimension_name,
  (case when h.hidden = 'N'
        then h.name else null end) hierarchy_name,
  fdhm.irid dim_hier_combo_id,
  u.username fact_table_owner,
  o.name fact_table_name,
  col.name column_name,
 decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                 2, decode(col.scale, null,
                           decode(col.precision#, null, 'NUMBER', 'FLOAT'),
                           'NUMBER'),
                 8, 'LONG',
                 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                 12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                 69, 'ROWID',
                 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'),
                 105, 'MLSLABEL',
                 106, 'MLSLABEL',
                 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'),
                 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                 178, 'TIME(' ||col.spare1|| ')',
                 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 180, 'TIMESTAMP(' ||col.spare1|| ')',
                 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE',
                 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH',
                 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' ||
                       col.spare1 || ')',
                 208, 'UROWID',
                 'UNDEFINED') data_type
, decode(col.length, null, 0, col.length) data_length
, decode(col.precision#, null, 0, col.precision#) data_precision
from olapsys.CwM2$Cube c,
     olapsys.CwM2$Dimension d,
     olapsys.CwM2$CubeDimensionUse cdu,
     olapsys.CwM2$FactDimHierMap fdhm,
     olapsys.CwM2$FactKeyDimHierMap fkdhm,
     olapsys.cwm2$factdimhiertplsdtl fdhtd,
     olapsys.cwm2$hierarchy h,
     sys.obj$ o,
     sys.col$ col,
     dba_users u
where c.irid = cdu.cube_irid and
      d.irid = cdu.dimension_irid and
      fdhm.cube_irid = c.irid and
      fkdhm.factDimHier_IRID = fdhm.irid and
      fkdhm.Dimension_IRID = d.irid and
      fdhtd.factdimhier_irid = fdhm.irid and
      fdhtd.hier_irid = h.irid and
      h.dimension_irid = d.irid and
      fdhm.FactTableName_ID = o.obj# and
      o.owner# = u.user_id and
      fkdhm.gidcolumnname_id = col.col# and
      o.obj# = col.obj#
with read only

View Text - HTML Formatted

SELECT DISTINCT C.OWNER OWNER
, C.NAME CUBE_NAME
, D.OWNER DIMENSION_OWNER
, D.NAME DIMENSION_NAME
, (CASE WHEN H.HIDDEN = 'N' THEN H.NAME ELSE NULL END) HIERARCHY_NAME
, FDHM.IRID DIM_HIER_COMBO_ID
, U.USERNAME FACT_TABLE_OWNER
, O.NAME FACT_TABLE_NAME
, COL.NAME COLUMN_NAME
, DECODE(COL.TYPE#
, 1
, DECODE(COL.CHARSETFORM
, 2
, 'NVARCHAR2'
, 'VARCHAR2')
, 2
, DECODE(COL.SCALE
, NULL
, DECODE(COL.PRECISION#
, NULL
, 'NUMBER'
, 'FLOAT')
, 'NUMBER')
, 8
, 'LONG'
, 9
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR VARYING'
, 'VARCHAR')
, 12
, 'DATE'
, 23
, 'RAW'
, 24
, 'LONG RAW'
, 69
, 'ROWID'
, 96
, DECODE(COL.CHARSETFORM
, 2
, 'NCHAR'
, 'CHAR')
, 105
, 'MLSLABEL'
, 106
, 'MLSLABEL'
, 112
, DECODE(COL.CHARSETFORM
, 2
, 'NCLOB'
, 'CLOB')
, 113
, 'BLOB'
, 114
, 'BFILE'
, 115
, 'CFILE'
, 178
, 'TIME(' ||COL.SPARE1|| ')'
, 179
, 'TIME(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 180
, 'TIMESTAMP(' ||COL.SPARE1|| ')'
, 181
, 'TIMESTAMP(' ||COL.SPARE1|| ')' || ' WITH TIME ZONE'
, 182
, 'INTERVAL YEAR(' ||COL.SPARE2||') TO MONTH'
, 183
, 'INTERVAL DAY(' ||COL.SPARE2||') TO SECOND(' || COL.SPARE1 || ')'
, 208
, 'UROWID'
, 'UNDEFINED') DATA_TYPE
, DECODE(COL.LENGTH
, NULL
, 0
, COL.LENGTH) DATA_LENGTH
, DECODE(COL.PRECISION#
, NULL
, 0
, COL.PRECISION#) DATA_PRECISION FROM OLAPSYS.CWM2$CUBE C
, OLAPSYS.CWM2$DIMENSION D
, OLAPSYS.CWM2$CUBEDIMENSIONUSE CDU
, OLAPSYS.CWM2$FACTDIMHIERMAP FDHM
, OLAPSYS.CWM2$FACTKEYDIMHIERMAP FKDHM
, OLAPSYS.CWM2$FACTDIMHIERTPLSDTL FDHTD
, OLAPSYS.CWM2$HIERARCHY H
, SYS.OBJ$ O
, SYS.COL$ COL
, DBA_USERS U WHERE C.IRID = CDU.CUBE_IRID AND D.IRID = CDU.DIMENSION_IRID AND FDHM.CUBE_IRID = C.IRID AND FKDHM.FACTDIMHIER_IRID = FDHM.IRID AND FKDHM.DIMENSION_IRID = D.IRID AND FDHTD.FACTDIMHIER_IRID = FDHM.IRID AND FDHTD.HIER_IRID = H.IRID AND H.DIMENSION_IRID = D.IRID AND FDHM.FACTTABLENAME_ID = O.OBJ# AND O.OWNER# = U.USER_ID AND FKDHM.GIDCOLUMNNAME_ID = COL.COL# AND O.OBJ# = COL.OBJ# WITH READ ONLY