select distinct lkcu.owner owner,
lkcu.dimension_name dimension_name,
dhl.hierarchy_name hierarchy_name,
lkcu.level_name child_level_name,
lkcu.table_owner table_owner,
lkcu.table_name table_name,
lkcu.column_name column_name,
lkcu.position position
from
(select u.username owner,
d.name dimension_name,
l.levelname level_name,
c.name column_name,
k.keypos# position,
tu.username table_owner,
tn.name table_name
from dba_users u,
sys.obj$ d,
sys.dimlevel$ l,
sys.dimlevelkey$ k,
sys.col$ c,
sys.obj$ tn,
dba_users tu,
olapsys.cwm$level lev
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
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
lev.dimension_irid = l.dimobj# and
lev.physicalname = l.levelname
) lkcu,
(select u.username dim_owner,
o.name dim_name,
h.hiername hierarchy_name,
dl.levelname level_name
from dba_users u,
sys.obj$ o,
sys.dim$ d,
sys.dimlevel$ dl,
sys.hier$ h,
sys.hierlevel$ hl,
olapsys.cwm$level lev
where u.user_id = o.owner# and
o.type# = 43 and
o.obj# = d.obj# and
d.obj# = dl.dimobj# and
d.obj# = h.dimobj# and
h.dimobj# = hl.dimobj# and
h.hierid# = hl.hierid# and
hl.levelid# = dl.levelid# and
lev.dimension_irid = dl.dimobj# and
lev.physicalname = dl.levelname
) dhl
where lkcu.owner = dhl.dim_owner (+) and
lkcu.dimension_name = dhl.dim_name (+) and
lkcu.level_name = dhl.level_name (+)
UNION ALL
select owner, dimension_name, hierarchy_name, child_level_name,
table_owner, table_name, column_name, position
from olapsys.all$olap2_level_key_col_uses
with read only
SELECT DISTINCT LKCU.OWNER OWNER
,
LKCU.DIMENSION_NAME DIMENSION_NAME
,
DHL.HIERARCHY_NAME HIERARCHY_NAME
,
LKCU.LEVEL_NAME CHILD_LEVEL_NAME
,
LKCU.TABLE_OWNER TABLE_OWNER
,
LKCU.TABLE_NAME TABLE_NAME
,
LKCU.COLUMN_NAME COLUMN_NAME
,
LKCU.POSITION POSITION
FROM
(SELECT U.USERNAME OWNER
,
D.NAME DIMENSION_NAME
,
L.LEVELNAME LEVEL_NAME
,
C.NAME COLUMN_NAME
,
K.KEYPOS# POSITION
,
TU.USERNAME TABLE_OWNER
,
TN.NAME TABLE_NAME
FROM DBA_USERS U
,
SYS.OBJ$ D
,
SYS.DIMLEVEL$ L
,
SYS.DIMLEVELKEY$ K
,
SYS.COL$ C
,
SYS.OBJ$ TN
,
DBA_USERS TU
,
OLAPSYS.CWM$LEVEL LEV
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
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
LEV.DIMENSION_IRID = L.DIMOBJ# AND
LEV.PHYSICALNAME = L.LEVELNAME
) LKCU
,
(SELECT U.USERNAME DIM_OWNER
,
O.NAME DIM_NAME
,
H.HIERNAME HIERARCHY_NAME
,
DL.LEVELNAME LEVEL_NAME
FROM DBA_USERS U
,
SYS.OBJ$ O
,
SYS.DIM$ D
,
SYS.DIMLEVEL$ DL
,
SYS.HIER$ H
,
SYS.HIERLEVEL$ HL
,
OLAPSYS.CWM$LEVEL LEV
WHERE U.USER_ID = O.OWNER# AND
O.TYPE# = 43 AND
O.OBJ# = D.OBJ# AND
D.OBJ# = DL.DIMOBJ# AND
D.OBJ# = H.DIMOBJ# AND
H.DIMOBJ# = HL.DIMOBJ# AND
H.HIERID# = HL.HIERID# AND
HL.LEVELID# = DL.LEVELID# AND
LEV.DIMENSION_IRID = DL.DIMOBJ# AND
LEV.PHYSICALNAME = DL.LEVELNAME
) DHL
WHERE LKCU.OWNER = DHL.DIM_OWNER (+) AND
LKCU.DIMENSION_NAME = DHL.DIM_NAME (+) AND
LKCU.LEVEL_NAME = DHL.LEVEL_NAME (+)
UNION ALL
SELECT OWNER
, DIMENSION_NAME
, HIERARCHY_NAME
, CHILD_LEVEL_NAME
,
TABLE_OWNER
, TABLE_NAME
, COLUMN_NAME
, POSITION
FROM OLAPSYS.ALL$OLAP2_LEVEL_KEY_COL_USES
WITH READ ONLY
|
|
|