VIEW: OLAPSYS.ALL$OLAPMR_FACTTBLKEYMAPS

Source

View Text - Preformatted

select a.owner owner,
       a.cube_name cube_name,
       a.dimension_owner dimension_owner,
       a.dimension_name dimension_name,
       a.level_name level_name,
       a.fact_table_owner fact_table_owner,
       a.fact_table_name fact_table_name,
       fk.column_name column_name,
       fk.position column_position,
       b.mv_summarycode mv_summary_code,
       fk.data_type data_type,
       fk.data_length data_length,
       fk.data_precision data_precision
from olapsys.all$olap1_fact_level_uses a,
     olapsys.all$olap1_cubes b,
     (select u.username table_owner,
             t.name table_name,
             c.name key_name,
             col.name column_name,
             ccol.pos# position,
             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
         dba_users u,
         sys.obj$ t,
         sys.con$ c,
         sys.cdef$ cd,
         sys.col$ col,
         sys.ccol$ ccol
      where
         u.user_id = c.owner# and
         c.con# = cd.con# and
         cd.con# = ccol.con# and
         cd.obj# = t.obj# and
         ccol.intcol# = col.intcol# and
         col.obj# = t.obj# and
         cd.type# in (2,3,4) and
         t.type# in (2,4)) fk
where
  a.fact_table_owner = fk.table_owner
  and a.fact_table_name = fk.table_name
  and a.foreign_key_name = fk.key_name
  and a.owner = b.owner
  and a.cube_name = b.cube_name
with read only

View Text - HTML Formatted

SELECT A.OWNER OWNER
, A.CUBE_NAME CUBE_NAME
, A.DIMENSION_OWNER DIMENSION_OWNER
, A.DIMENSION_NAME DIMENSION_NAME
, A.LEVEL_NAME LEVEL_NAME
, A.FACT_TABLE_OWNER FACT_TABLE_OWNER
, A.FACT_TABLE_NAME FACT_TABLE_NAME
, FK.COLUMN_NAME COLUMN_NAME
, FK.POSITION COLUMN_POSITION
, B.MV_SUMMARYCODE MV_SUMMARY_CODE
, FK.DATA_TYPE DATA_TYPE
, FK.DATA_LENGTH DATA_LENGTH
, FK.DATA_PRECISION DATA_PRECISION FROM OLAPSYS.ALL$OLAP1_FACT_LEVEL_USES A
, OLAPSYS.ALL$OLAP1_CUBES B
, (SELECT U.USERNAME TABLE_OWNER
, T.NAME TABLE_NAME
, C.NAME KEY_NAME
, COL.NAME COLUMN_NAME
, CCOL.POS# POSITION
, 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 DBA_USERS U
, SYS.OBJ$ T
, SYS.CON$ C
, SYS.CDEF$ CD
, SYS.COL$ COL
, SYS.CCOL$ CCOL WHERE U.USER_ID = C.OWNER# AND C.CON# = CD.CON# AND CD.CON# = CCOL.CON# AND CD.OBJ# = T.OBJ# AND CCOL.INTCOL# = COL.INTCOL# AND COL.OBJ# = T.OBJ# AND CD.TYPE# IN (2
, 3
, 4) AND T.TYPE# IN (2
, 4)) FK WHERE A.FACT_TABLE_OWNER = FK.TABLE_OWNER
AND A.FACT_TABLE_NAME = FK.TABLE_NAME
AND A.FOREIGN_KEY_NAME = FK.KEY_NAME
AND A.OWNER = B.OWNER
AND A.CUBE_NAME = B.CUBE_NAME WITH READ ONLY