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,
level_name level_name
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,
l.levelname level_name
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
(cwm$util.dimension_tables_visible(d.obj#) = 'Y'
OR EXISTS
(select null from v$enabledprivs
where priv_number in (-47, -215, -216, -217))) 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,
l.name level_name
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.invalid = 'N' and
(cwm2$security.dimension_tables_visible(d.irid) = 'Y'
OR EXISTS (select null from v$enabledprivs
where priv_number in (-47, -215, -216, -217)))
AND d.irid = ce.element_irid (+) and
ce.name (+) = 'DIMENSION2'
)
with read only
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
,
LEVEL_NAME LEVEL_NAME
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
,
L.LEVELNAME LEVEL_NAME
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
(CWM$UTIL.DIMENSION_TABLES_VISIBLE(D.OBJ#) = 'Y'
OR EXISTS
(SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217))) 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
,
L.NAME LEVEL_NAME
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.INVALID = 'N' AND
(CWM2$SECURITY.DIMENSION_TABLES_VISIBLE(D.IRID) = 'Y'
OR EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217)))
AND D.IRID = CE.ELEMENT_IRID (+) AND
CE.NAME (+) = 'DIMENSION2'
)
WITH READ ONLY
|
|
|