select distinct
ce.classification_irid catalog_id
, sch.physicalname entity_owner
, cub.physicalname entity_name
, msr.physicalname child_entity_name
, ce.name entry_name -- added for export
FROM
olapsys.cwm$classification c
, olapsys.cwm$classificationentry ce
, olapsys.cwm$classificationtype cty
, olapsys.cwm$measure msr
, olapsys.cwm$cube cub
, olapsys.cwm$model sch
, dba_users u
, olapsys.cwm$cubedimensionuse cdu
, olapsys.cwm$dimension cd
, dba_users du
, sys.obj$ do
WHERE cty.irid = c.classificationtype_irid
AND cty.name = 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'MEASURE'
AND ce.element_irid = msr.irid
AND msr.itemcontainer_irid = cub.irid
AND sch.irid = cub.datamodel_irid
AND sch.physicalname = u.username
AND cdu.cube_irid = cub.irid
AND cdu.dimension_owner = du.username
AND cdu.dimension_name = do.name
AND du.user_id = do.owner#
AND do.obj# = cd.irid
AND do.type# = 43
AND cd.irid = cdu.abstractdimension_irid
union all
select catalog_id,
entity_owner,
entity_name,
child_entity_name,
entry_name -- added
from olapsys.EXPORT2_catalog_entity_uses
with read only
SELECT DISTINCT
CE.CLASSIFICATION_IRID CATALOG_ID
, SCH.PHYSICALNAME ENTITY_OWNER
, CUB.PHYSICALNAME ENTITY_NAME
, MSR.PHYSICALNAME CHILD_ENTITY_NAME
, CE.NAME ENTRY_NAME -- ADDED FOR EXPORT
FROM
OLAPSYS.CWM$CLASSIFICATION C
, OLAPSYS.CWM$CLASSIFICATIONENTRY CE
, OLAPSYS.CWM$CLASSIFICATIONTYPE CTY
, OLAPSYS.CWM$MEASURE MSR
, OLAPSYS.CWM$CUBE CUB
, OLAPSYS.CWM$MODEL SCH
, DBA_USERS U
, OLAPSYS.CWM$CUBEDIMENSIONUSE CDU
, OLAPSYS.CWM$DIMENSION CD
, DBA_USERS DU
, SYS.OBJ$ DO
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME = 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'MEASURE'
AND CE.ELEMENT_IRID = MSR.IRID
AND MSR.ITEMCONTAINER_IRID = CUB.IRID
AND SCH.IRID = CUB.DATAMODEL_IRID
AND SCH.PHYSICALNAME = U.USERNAME
AND CDU.CUBE_IRID = CUB.IRID
AND CDU.DIMENSION_OWNER = DU.USERNAME
AND CDU.DIMENSION_NAME = DO.NAME
AND DU.USER_ID = DO.OWNER#
AND DO.OBJ# = CD.IRID
AND DO.TYPE# = 43
AND CD.IRID = CDU.ABSTRACTDIMENSION_IRID
UNION ALL
SELECT CATALOG_ID
,
ENTITY_OWNER
,
ENTITY_NAME
,
CHILD_ENTITY_NAME
,
ENTRY_NAME -- ADDED
FROM OLAPSYS.EXPORT2_CATALOG_ENTITY_USES
WITH READ ONLY
|
|
|