select
d.owner owner,
d.name dimension_name,
(case when h.hidden = 'N'
then h.name else null end) hierarchy_name,
l.name child_level_name,
u.username table_owner,
o.name table_name,
c.name column_name,
dhlm.position position
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
where h.dimension_irid = d.irid 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)))
with read only
SELECT
D.OWNER OWNER
,
D.NAME DIMENSION_NAME
,
(CASE WHEN H.HIDDEN = 'N'
THEN H.NAME ELSE NULL END) HIERARCHY_NAME
,
L.NAME CHILD_LEVEL_NAME
,
U.USERNAME TABLE_OWNER
,
O.NAME TABLE_NAME
,
C.NAME COLUMN_NAME
,
DHLM.POSITION POSITION
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
WHERE H.DIMENSION_IRID = D.IRID 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)))
WITH READ ONLY
|
|
|