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# and
(c.invalid = 'N' or c.invalid = 'O') and
(cwm2$security.fact_table_visible(c.irid) = 'Y'
OR EXISTS (select null from v$enabledprivs
where priv_number in (-47)))
with read only
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# AND
(C.INVALID = 'N' OR C.INVALID = 'O') AND
(CWM2$SECURITY.FACT_TABLE_VISIBLE(C.IRID) = 'Y'
OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47)))
WITH READ ONLY
|
|
|