SELECT
a.u_name owner
, a.d_name dimension_name
, a.l_levelname level_name
, nvl(la.lat_name, a.c_name) attribute_name
, a.c_name column_name
, 1 position
FROM
(SELECT
u.username u_name
, d.name d_name
, l.dimobj# l_dimobj#
, l.levelname l_levelname
, c.obj# c_obj#
, c.name c_name
FROM
dba_users u
, sys.obj$ d
, sys.dimlevel$ l
, sys.dimattr$ a
, sys.col$ c
WHERE u.user_id = d.owner#
AND d.type# = 43 /* DIMENSION */
AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y'
OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */
(SELECT null FROM v$enabledprivs
WHERE priv_number IN (-47,-215,-216,-217)))
AND d.obj# = l.dimobj#
AND l.dimobj# = a.dimobj#
AND l.levelid# = a.levelid#
AND a.detailobj# = c.obj#
AND a.col# = c.col#) a
, (SELECT
lat.name lat_name
, lat.displayname lat_displayname
, lat.description lat_description
, lat.type_irid lat_type_irid
, lat.physicalname lat_physicalname
, lev.dimension_irid lev_dimension_irid
, lev.physicalname lev_physicalname
FROM cwm$level lev, cwm$levelattribute lat
WHERE lev.irid = lat.itemcontainer_irid) la
WHERE a.l_dimobj# = la.lev_dimension_irid (+)
AND a.l_levelname = la.lev_physicalname (+)
AND a.c_obj# = la.lat_type_irid (+)
AND a.c_name = la.lat_physicalname (+)
WITH READ ONLY
SELECT
A.U_NAME OWNER
, A.D_NAME DIMENSION_NAME
, A.L_LEVELNAME LEVEL_NAME
, NVL(LA.LAT_NAME
, A.C_NAME) ATTRIBUTE_NAME
, A.C_NAME COLUMN_NAME
, 1 POSITION
FROM
(SELECT
U.USERNAME U_NAME
, D.NAME D_NAME
, L.DIMOBJ# L_DIMOBJ#
, L.LEVELNAME L_LEVELNAME
, C.OBJ# C_OBJ#
, C.NAME C_NAME
FROM
DBA_USERS U
, SYS.OBJ$ D
, SYS.DIMLEVEL$ L
, SYS.DIMATTR$ A
, SYS.COL$ C
WHERE U.USER_ID = D.OWNER#
AND D.TYPE# = 43 /* DIMENSION */
AND ( CWM$UTIL.DIMENSION_TABLES_VISIBLE(D.OBJ#) = 'Y'
OR EXISTS /* SELECT ANY TABLE
, CREATE
, ALTER
, DROP ANY DIMENSION */
(SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-47
, -215
, -216
, -217)))
AND D.OBJ# = L.DIMOBJ#
AND L.DIMOBJ# = A.DIMOBJ#
AND L.LEVELID# = A.LEVELID#
AND A.DETAILOBJ# = C.OBJ#
AND A.COL# = C.COL#) A
, (SELECT
LAT.NAME LAT_NAME
, LAT.DISPLAYNAME LAT_DISPLAYNAME
, LAT.DESCRIPTION LAT_DESCRIPTION
, LAT.TYPE_IRID LAT_TYPE_IRID
, LAT.PHYSICALNAME LAT_PHYSICALNAME
, LEV.DIMENSION_IRID LEV_DIMENSION_IRID
, LEV.PHYSICALNAME LEV_PHYSICALNAME
FROM CWM$LEVEL LEV
, CWM$LEVELATTRIBUTE LAT
WHERE LEV.IRID = LAT.ITEMCONTAINER_IRID) LA
WHERE A.L_DIMOBJ# = LA.LEV_DIMENSION_IRID (+)
AND A.L_LEVELNAME = LA.LEV_PHYSICALNAME (+)
AND A.C_OBJ# = LA.LAT_TYPE_IRID (+)
AND A.C_NAME = LA.LAT_PHYSICALNAME (+)
WITH READ ONLY
|
|
|