SELECT
ce.classification_irid descriptor_id
, sch.physicalname entity_owner
, cub.physicalname entity_name
, null child_entity_name
, NULL secondary_child_entity_name
FROM /* CUBE */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$cube cub
, cwm$model sch
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'CUBE'
AND ce.element_irid = cub.irid
AND cub.datamodel_irid = sch.irid
AND sch.physicalname = u.username
AND ( cwm$util.fact_table_visible(cub.irid) = 'Y'
OR EXISTS /* SELECT ANY TABLE */
(SELECT null FROM v$enabledprivs
WHERE priv_number = -47))
UNION ALL SELECT
ce.classification_irid descriptor_id
, sch.physicalname entity_owner
, cub.physicalname entity_name
, msr.physicalname child_entity_name
, NULL secondary_child_entity_name
FROM /* MEASURE */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$measure msr
, cwm$cube cub
, cwm$model sch
, dba_users u
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 cub.datamodel_irid = sch.irid
AND sch.physicalname = u.username
AND ( cwm$util.fact_table_visible(cub.irid) = 'Y'
OR EXISTS /* SELECT ANY TABLE */
(SELECT null FROM v$enabledprivs
WHERE priv_number = -47))
UNION ALL SELECT
ce.classification_irid descriptor_id
, u.username entity_owner
, d.name entity_name
, dat.physicalname child_entity_name
, NULL secondary_child_entity_name
FROM /* DIMENSIONATTRIBUTE */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$dimensionattribute dat
, sys.obj$ d
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'DIMENSION ATTRIBUTE'
AND ce.element_irid = dat.irid
AND dat.itemcontainer_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
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)))
UNION ALL SELECT
ce.classification_irid descriptor_id
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, ce.tertiary_object_name secondary_child_entity_name
FROM /* LEVELATTRIBUTE */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$levelattribute lat
, cwm$level lvl
, sys.obj$ d
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'LEVEL ATTRIBUTE'
AND ce.element_irid = lat.irid
AND lat.itemcontainer_irid = lvl.irid
AND lvl.dimension_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
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)))
UNION ALL SELECT
ce.classification_irid descriptor_id
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, null secondary_child_entity_name
FROM /* DIMENSION, LEVEL, HIERARCHY */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, sys.obj$ d
, dba_users u
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name IN ('DIMENSION', 'LEVEL', 'HIERARCHY')
AND ce.element_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
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)))
UNION ALL SELECT
ce.classification_irid descriptor_id
, f.name entity_owner
, p.name entity_name
, null child_entity_name
, NULL secondary_child_entity_name
FROM /* FUNCTION PARAMETER */
cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$function f
, cwm$parameter p
WHERE cty.irid = c.classificationtype_irid
AND cty.name <> 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.classification_irid
AND ce.name = 'PARAMETER'
AND ce.element_irid = p.irid
AND p.operation_irid = f.irid
WITH READ ONLY
SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, SCH.PHYSICALNAME ENTITY_OWNER
, CUB.PHYSICALNAME ENTITY_NAME
, NULL CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
FROM /* CUBE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM$CUBE CUB
, CWM$MODEL SCH
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'CUBE'
AND CE.ELEMENT_IRID = CUB.IRID
AND CUB.DATAMODEL_IRID = SCH.IRID
AND SCH.PHYSICALNAME = U.USERNAME
AND ( CWM$UTIL.FACT_TABLE_VISIBLE(CUB.IRID) = 'Y'
OR EXISTS /* SELECT ANY TABLE */
(SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -47))
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, SCH.PHYSICALNAME ENTITY_OWNER
, CUB.PHYSICALNAME ENTITY_NAME
, MSR.PHYSICALNAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
FROM /* MEASURE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM$MEASURE MSR
, CWM$CUBE CUB
, CWM$MODEL SCH
, DBA_USERS U
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 CUB.DATAMODEL_IRID = SCH.IRID
AND SCH.PHYSICALNAME = U.USERNAME
AND ( CWM$UTIL.FACT_TABLE_VISIBLE(CUB.IRID) = 'Y'
OR EXISTS /* SELECT ANY TABLE */
(SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -47))
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, DAT.PHYSICALNAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
FROM /* DIMENSIONATTRIBUTE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM$DIMENSIONATTRIBUTE DAT
, SYS.OBJ$ D
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'DIMENSION ATTRIBUTE'
AND CE.ELEMENT_IRID = DAT.IRID
AND DAT.ITEMCONTAINER_IRID = D.OBJ#
AND D.TYPE# = 43 /* DIMENSION */
AND D.OWNER# = U.USER_ID
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)))
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
FROM /* LEVELATTRIBUTE */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM$LEVELATTRIBUTE LAT
, CWM$LEVEL LVL
, SYS.OBJ$ D
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'LEVEL ATTRIBUTE'
AND CE.ELEMENT_IRID = LAT.IRID
AND LAT.ITEMCONTAINER_IRID = LVL.IRID
AND LVL.DIMENSION_IRID = D.OBJ#
AND D.TYPE# = 43 /* DIMENSION */
AND D.OWNER# = U.USER_ID
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)))
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
FROM /* DIMENSION
, LEVEL
, HIERARCHY */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, SYS.OBJ$ D
, DBA_USERS U
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME IN ('DIMENSION'
, 'LEVEL'
, 'HIERARCHY')
AND CE.ELEMENT_IRID = D.OBJ#
AND D.TYPE# = 43 /* DIMENSION */
AND D.OWNER# = U.USER_ID
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)))
UNION ALL SELECT
CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, F.NAME ENTITY_OWNER
, P.NAME ENTITY_NAME
, NULL CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
FROM /* FUNCTION PARAMETER */
CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM$FUNCTION F
, CWM$PARAMETER P
WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID
AND CTY.NAME <> 'ORACLE_OLAP_CATALOG'
AND C.IRID = CE.CLASSIFICATION_IRID
AND CE.NAME = 'PARAMETER'
AND CE.ELEMENT_IRID = P.IRID
AND P.OPERATION_IRID = F.IRID
WITH READ ONLY
|
|
|