VIEW: OLAPSYS.DBA$OLAP2UENTITY_EXT_PARMS

Source

View Text - Preformatted

SELECT
  ce.classification_irid descriptor_id
, c.name descriptor_name
, sch.physicalname entity_owner
, cub.physicalname entity_name
, ce.secondary_object_name child_entity_name
, ce.tertiary_object_name secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
, null parameter_value2
, null parameter_value3
, null parameter_value4
, null position
FROM /* DENSE INDICATOR */
  cwm$classification c
, cwm$classificationentry ce
, cwm2$classificationvaluepair cvp
, cwm$classificationtype cty
, cwm$cube cub
, cwm$cubedimensionuse cdu
, cwm$model sch
, dba_users u
, 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 = 'DENSE INDICATOR'
AND ce.element_irid = cdu.irid
AND cub.irid = cdu.cube_irid
AND cdu.dimension_name = ce.tertiary_object_name
AND ce.irid = cvp.classentry_irid (+)
AND cub.datamodel_irid = sch.irid
AND sch.physicalname = u.username
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
 ce.classification_irid descriptor_id,
 c.name descriptor_name,
 cub.owner entity_owner,
 cub.name entity_name,
 ce.secondary_object_name child_entity_name,
 ce.tertiary_object_name secondary_child_entity_name,
 cvp.parametername parameter_name,
 cvp.parametervalue parameter_value,
 null parameter_value2,
 null parameter_value3,
 null parameter_value4,
 null position
from /* DENSE INDICATOR2 */
  cwm$classification c,
  cwm$classificationentry ce,
  cwm2$classificationvaluepair cvp,
  cwm$classificationtype cty,
  cwm2$cube cub,
  cwm2$dimension dim,
  cwm2$cubedimensionuse cdu
where cty.irid = c.classificationtype_irid and
      cty.name <> 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.classification_irid and
      ce.name = 'DENSE INDICATOR2' and
      ce.element_irid = cdu.irid and
      cub.irid = cdu.cube_irid and
      ce.tertiary_object_name = dim.name and
      dim.irid = cdu.dimension_irid and
      ce.irid = cvp.classentry_irid (+)
UNION ALL SELECT
  ce.classification_irid descriptor_id
, c.name descriptor_name
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, null secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
, cvp.parametervalue2 parameter_value2
, null parameter_value3
, null parameter_value4
, cvp.position position
FROM /* DEFAULT MEMBER */
  cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm2$classificationvaluepair cvp
, cwm$hierarchy hier
, 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.irid = cvp.classentry_irid (+)
AND ce.name = 'DEFAULT MEMBER'
AND ce.element_irid = hier.irid
AND hier.dimension_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
UNION ALL SELECT
  ce.classification_irid descriptor_id
, c.name descriptor_name
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, null secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
, cvp.parametervalue2 parameter_value2
, null parameter_value3
, null parameter_value4
, cvp.position position
FROM /* DEFAULT MEMBER */
  cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm2$classificationvaluepair cvp
, 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.irid = cvp.classentry_irid (+)
AND ce.name = 'DEFAULT MEMBER'
AND ce.element_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
UNION ALL SELECT
 ce.classification_irid descriptor_id,
 c.name descriptor_name,
 dim.owner entity_owner,
 dim.name entity_name,
 ce.secondary_object_name child_entity_name,
 null secondary_child_entity_name,
 cvp.parametername parameter_name,
 cvp.parametervalue parameter_value,
 cvp.parametervalue2 parameter_value2,
 null parameter_value3,
 null parameter_value4,
 cvp.position position
from /* DEFAULT MEMBER2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm2$classificationvaluepair cvp,
 cwm$classificationtype cty,
 cwm2$dimension dim,
 cwm2$hierarchy hier
where cty.irid = c.classificationtype_irid and
      cty.name not in ('ORACLE_OLAP2_CATALOG',
                       'ORACLE_OLAP_CATALOG') and
      c.irid = ce.classification_irid and
      ce.irid = cvp.classentry_irid (+) and
      ce.name  = 'DEFAULT MEMBER2' and
      ce.element_irid = hier.irid and
      hier.dimension_irid = dim.irid
UNION ALL SELECT
  ce.classification_irid descriptor_id
, c.name descriptor_name
, u.username entity_owner
, d.name entity_name
, ce.secondary_object_name child_entity_name
, ce.tertiary_object_name secondary_child_entity_name
, cvp.parametername parameter_name
, cvp.parametervalue parameter_value
, null parameter_value2
, null parameter_value3
, null parameter_value4
, null position
FROM /* ESTIMATED CARDINALITY */
  cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm2$classificationvaluepair cvp
, cwm$level lev
, 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.irid = cvp.classentry_irid (+)
AND ce.name = 'ESTIMATED CARDINALITY'
AND ce.element_irid = lev.irid
AND lev.dimension_irid = d.obj#
AND d.type# = 43 /* DIMENSION */
AND d.owner# = u.user_id
UNION ALL SELECT
 ce.classification_irid descriptor_id,
 c.name descriptor_name,
 dim.owner entity_owner,
 dim.name entity_name,
 ce.secondary_object_name child_entity_name,
 ce.tertiary_object_name secondary_child_entity_name,
 cvp.parametername parameter_name,
 cvp.parametervalue parameter_value,
 null parameter_value2,
 null parameter_value3,
 null parameter_value4,
 null position
from /* ESTIMATED CARDINALITY2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm2$classificationvaluepair cvp,
 cwm$classificationtype cty,
 cwm2$dimension dim,
 cwm2$hierlevelrel hlr
where cty.irid = c.classificationtype_irid and
      cty.name not in ('ORACLE_OLAP2_CATALOG',
                       'ORACLE_OLAP_CATALOG') and
      c.irid = ce.classification_irid and
      ce.irid = cvp.classentry_irid (+) and
      ce.name  = 'ESTIMATED CARDINALITY2' and
      ce.element_irid = hlr.irid and
      hlr.dimension_irid = dim.irid
UNION ALL select
 ce.classification_irid descriptor_id,
 c.name descriptor_name,
 cub.owner entity_owner,
 cub.name entity_name,
 ce.secondary_object_name child_entity_name,
 ce.tertiary_object_name secondary_child_entity_name,
 cvp.parametername parameter_name,
 cvp.parametervalue parameter_value,
 cvp.parametervalue2 parameter_value2,
 cvp.parametervalue3 parameter_value3,
 cvp.parametervalue4 parameter_value4,
 cvp.position position
from /* FACT TABLE JOIN2 */
  cwm$classification c,
  cwm$classificationentry ce,
  cwm2$classificationvaluepair cvp,
  cwm$classificationtype cty,
  cwm2$cube cub,
  cwm2$dimension dim,
  cwm2$cubedimensionuse cdu
where cty.irid = c.classificationtype_irid and
      cty.name <> 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.classification_irid and
      ce.name = 'FACT TABLE JOIN2' and
      ce.element_irid = cdu.irid and
      cub.irid = cdu.cube_irid and
      ce.tertiary_object_name = dim.name and
      dim.irid = cdu.dimension_irid and
      ce.irid = cvp.classentry_irid (+)
with read only

View Text - HTML Formatted

SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, SCH.PHYSICALNAME ENTITY_OWNER
, CUB.PHYSICALNAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, NULL PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, NULL POSITION FROM /* DENSE INDICATOR */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM$CUBE CUB
, CWM$CUBEDIMENSIONUSE CDU
, CWM$MODEL SCH
, DBA_USERS U
, 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 = 'DENSE INDICATOR' AND CE.ELEMENT_IRID = CDU.IRID AND CUB.IRID = CDU.CUBE_IRID AND CDU.DIMENSION_NAME = CE.TERTIARY_OBJECT_NAME AND CE.IRID = CVP.CLASSENTRY_IRID (+) AND CUB.DATAMODEL_IRID = SCH.IRID AND SCH.PHYSICALNAME = U.USERNAME 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 CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, CUB.OWNER ENTITY_OWNER
, CUB.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, NULL PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, NULL POSITION FROM /* DENSE INDICATOR2 */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CUBE CUB
, CWM2$DIMENSION DIM
, CWM2$CUBEDIMENSIONUSE CDU WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND CTY.NAME <> 'ORACLE_OLAP2_CATALOG' AND C.IRID = CE.CLASSIFICATION_IRID AND CE.NAME = 'DENSE INDICATOR2' AND CE.ELEMENT_IRID = CDU.IRID AND CUB.IRID = CDU.CUBE_IRID AND CE.TERTIARY_OBJECT_NAME = DIM.NAME AND DIM.IRID = CDU.DIMENSION_IRID AND CE.IRID = CVP.CLASSENTRY_IRID (+) UNION ALL SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, CVP.PARAMETERVALUE2 PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, CVP.POSITION POSITION FROM /* DEFAULT MEMBER */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$HIERARCHY HIER
, 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.IRID = CVP.CLASSENTRY_IRID (+) AND CE.NAME = 'DEFAULT MEMBER' AND CE.ELEMENT_IRID = HIER.IRID AND HIER.DIMENSION_IRID = D.OBJ# AND D.TYPE# = 43 /* DIMENSION */ AND D.OWNER# = U.USER_ID UNION ALL SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, CVP.PARAMETERVALUE2 PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, CVP.POSITION POSITION FROM /* DEFAULT MEMBER */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, 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.IRID = CVP.CLASSENTRY_IRID (+) AND CE.NAME = 'DEFAULT MEMBER' AND CE.ELEMENT_IRID = D.OBJ# AND D.TYPE# = 43 /* DIMENSION */ AND D.OWNER# = U.USER_ID UNION ALL SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, DIM.OWNER ENTITY_OWNER
, DIM.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, NULL SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, CVP.PARAMETERVALUE2 PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, CVP.POSITION POSITION FROM /* DEFAULT MEMBER2 */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$DIMENSION DIM
, CWM2$HIERARCHY HIER WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND CTY.NAME NOT IN ('ORACLE_OLAP2_CATALOG'
, 'ORACLE_OLAP_CATALOG') AND C.IRID = CE.CLASSIFICATION_IRID AND CE.IRID = CVP.CLASSENTRY_IRID (+) AND CE.NAME = 'DEFAULT MEMBER2' AND CE.ELEMENT_IRID = HIER.IRID AND HIER.DIMENSION_IRID = DIM.IRID UNION ALL SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, U.USERNAME ENTITY_OWNER
, D.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, NULL PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, NULL POSITION FROM /* ESTIMATED CARDINALITY */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$LEVEL LEV
, 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.IRID = CVP.CLASSENTRY_IRID (+) AND CE.NAME = 'ESTIMATED CARDINALITY' AND CE.ELEMENT_IRID = LEV.IRID AND LEV.DIMENSION_IRID = D.OBJ# AND D.TYPE# = 43 /* DIMENSION */ AND D.OWNER# = U.USER_ID UNION ALL SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, DIM.OWNER ENTITY_OWNER
, DIM.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, NULL PARAMETER_VALUE2
, NULL PARAMETER_VALUE3
, NULL PARAMETER_VALUE4
, NULL POSITION FROM /* ESTIMATED CARDINALITY2 */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$DIMENSION DIM
, CWM2$HIERLEVELREL HLR WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND CTY.NAME NOT IN ('ORACLE_OLAP2_CATALOG'
, 'ORACLE_OLAP_CATALOG') AND C.IRID = CE.CLASSIFICATION_IRID AND CE.IRID = CVP.CLASSENTRY_IRID (+) AND CE.NAME = 'ESTIMATED CARDINALITY2' AND CE.ELEMENT_IRID = HLR.IRID AND HLR.DIMENSION_IRID = DIM.IRID UNION ALL SELECT CE.CLASSIFICATION_IRID DESCRIPTOR_ID
, C.NAME DESCRIPTOR_NAME
, CUB.OWNER ENTITY_OWNER
, CUB.NAME ENTITY_NAME
, CE.SECONDARY_OBJECT_NAME CHILD_ENTITY_NAME
, CE.TERTIARY_OBJECT_NAME SECONDARY_CHILD_ENTITY_NAME
, CVP.PARAMETERNAME PARAMETER_NAME
, CVP.PARAMETERVALUE PARAMETER_VALUE
, CVP.PARAMETERVALUE2 PARAMETER_VALUE2
, CVP.PARAMETERVALUE3 PARAMETER_VALUE3
, CVP.PARAMETERVALUE4 PARAMETER_VALUE4
, CVP.POSITION POSITION FROM /* FACT TABLE JOIN2 */ CWM$CLASSIFICATION C
, CWM$CLASSIFICATIONENTRY CE
, CWM2$CLASSIFICATIONVALUEPAIR CVP
, CWM$CLASSIFICATIONTYPE CTY
, CWM2$CUBE CUB
, CWM2$DIMENSION DIM
, CWM2$CUBEDIMENSIONUSE CDU WHERE CTY.IRID = C.CLASSIFICATIONTYPE_IRID AND CTY.NAME <> 'ORACLE_OLAP2_CATALOG' AND C.IRID = CE.CLASSIFICATION_IRID AND CE.NAME = 'FACT TABLE JOIN2' AND CE.ELEMENT_IRID = CDU.IRID AND CUB.IRID = CDU.CUBE_IRID AND CE.TERTIARY_OBJECT_NAME = DIM.NAME AND DIM.IRID = CDU.DIMENSION_IRID AND CE.IRID = CVP.CLASSENTRY_IRID (+) WITH READ ONLY