Rem
Rem cwm2expv.sql
Rem
Rem Copyright (c) 2003, 2004, Oracle Corporation.  All rights reserved.  
Rem
Rem    NAME
Rem      cwm2expv.sql - export views
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    glyon       06/24/08 - bug 7204558: eliminate references to sys.user$
Rem    awesley     03/10/04 - add to export views
Rem    awesley     01/19/04 - add comments
Rem    awesley     12/05/03 - awesley_txn109857
Rem    awesley     10/07/03 - Created
Rem


-- shoud be same as olapsys.dba$olap1_cubes except as noted
CREATE OR REPLACE VIEW olapsys.EXPORTolap1_cubes
 AS SELECT DISTINCT
   sch.physicalname owner
   , cub.physicalname cube_name
   , cwm$util.cube_invalid(cub.irid) invalid
   , cub.displayname display_name
   , cub.description short_description
   , cub.description description
   , nvl(cub.mvsummarycode, 'RU') mv_summarycode
   , cub.irid irid   -- add for export
   , 'CWM1' model    -- add for export
FROM
   dba_users u
  , cwm$model sch
  , cwm$cube  cub
  , cwm$cubedimensionuse cdu
  , cwm$dimension cd
  , dba_users du
  , sys.obj$ do
WHERE u.username = sch.physicalname
    AND sch.irid = cub.datamodel_irid
    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
WITH READ ONLY
/
show errors;


-- shoud be same as olapsys.dba$olap2_Cubes except as noted 
create or replace view olapsys.EXPORTolap2_Cubes as select
  c.owner owner,
  c.name cube_name,
  c.invalid invalid,
  c.displayname display_name,
  c.shortdescription short_description,
  c.description description,
  nvl(c.MVSummaryCode, 'GS') mv_summarycode,
  c.irid irid,  -- added for export
  'CWM2' model  -- added for export
from olapsys.CwM2$Cube c
with read only
/
show errors;


-- shoud be same as olapsys.dba$olap2ucubes except as noted
create or replace view olapsys.EXPORTolap2ucubes as
select owner, cube_name, invalid, display_name, short_description,
       description, mv_summarycode,
       irid, model              -- added for export
from olapsys.EXPORTolap1_cubes  -- changed for export
union all
select owner, cube_name, invalid, display_name, short_description,
       description, mv_summarycode,
       irid, model              -- added for export
from olapsys.EXPORTolap2_cubes  -- changed for export
with read only
/
show errors;




-- should be same as olapsys.dba$olapmr_dimensions except as noted
CREATE OR REPLACE VIEW olapsys.EXPORTolapmr_dimensions AS SELECT
  u.username                  owner, 
  o.name                  dimension_name,
  dim.pluralname          plural_name,
  dim.displayname         display_name,
  dim.description         short_description,
  dim.description         description,
  display_hierarchy_name  default_display_hierarchy,
  o.status                status,
  dim.irid irid,  -- added for export
  'CWM1' model    -- added for export
FROM 
  sys.dim$                d,
  dba_users               u,
  sys.obj$                o,
  cwm$dimension           dim
WHERE o.type# = 43 AND
      u.user_id = o.owner# AND
      o.obj#  = d.obj# AND
      d.obj#  = dim.irid
WITH READ ONLY
/
show errors;


-- should be same as olapsys.dba$olap2_Dimensions except as noted
create or replace view olapsys.EXPORTolap2_Dimensions as select
  d.owner owner,
  d.name dimension_name,
  d.pluralname plural_name,
  d.displayname display_name,
  d.shortdescription short_description,
  d.description description,
  (case when h.hidden = 'N'
        then h.name else null end) default_display_hierarchy,
  d.invalid invalid,
  d.dimensiontype dimension_type,
  d.irid irid,  -- added for export
  'CWM2' model  -- added for export
from olapsys.CwM2$dimension d,
     olapsys.CwM2$hierarchy h
where 
       d.DefaultHier_IRID = h.irid (+)
with read only
/
show errors;


-- should be same as olapsys.dba$olap2udimensions except as noted
create or replace view olapsys.EXPORTolap2udimensions as
select mrd.owner owner, 
       mrd.dimension_name dimension_name, 
       mrd.plural_name plural_name, 
       mrd.display_name display_name, 
       mrd.short_description short_description,
       mrd.description description,
       mrd.default_display_hierarchy default_display_hierarchy, 
       decode(mrd.status, 5, 'Y', 'N') invalid, 
       'HIER' dimension_type,
       irid,                              -- added for export
       model                              -- added for export
from olapsys.EXPORTolapmr_dimensions mrd  -- changed for export
union all
select owner, dimension_name, plural_name, display_name, short_description,
       description, default_display_hierarchy, invalid, dimension_type,
       irid, model                        -- added for export
from olapsys.EXPORTolap2_dimensions       -- changed for export
with read only 
/
show errors;




create or replace view olapsys.EXPORT2_Dim_Hier_Level_Uses as select
  d.owner owner,
  d.name dimension_name,
--  (case when h.hidden = 'Y'
--        then null else h.name end) hierarchy_name,
  h.name hierarchy_name,
  pl.name parent_level_name,
  cl.name child_level_name,
  hlr.leveldepth position
from olapsys.CwM2$dimension d,
     olapsys.CwM2$level pl,
     olapsys.CwM2$level cl,
     olapsys.CwM2$HierLevelRel hlr,
     olapsys.CwM2$Hierarchy h
where hlr.dimension_irid = d.irid and
      hlr.hierarchy_IRID = h.irid and
      hlr.parentlevel_irid = pl.irid(+) and
      hlr.childlevel_irid = cl.irid
with read only
/
show errors;


create or replace view olapsys.EXPORT2udim_hier_level_uses as
SELECT
  u.username owner
, d.name dimension_name
, h.hiername hierarchy_name
, pdl.levelname parent_level_name
, cdl.levelname child_level_name
, chl.pos# position
FROM
  dba_users u
, sys.obj$ d
, sys.hier$ h
, sys.hierlevel$ phl
, sys.hierlevel$ chl
, sys.dimlevel$ pdl
, sys.dimlevel$ cdl
, olapsys.cwm$hierarchy ch
WHERE d.type# = 43 /* DIMENSION */
AND u.user_id = d.owner#
AND d.obj# = h.dimobj#
AND d.obj# = phl.dimobj#
AND h.hierid# = phl.hierid#
AND d.obj# = pdl.dimobj#
AND phl.levelid# = pdl.levelid#
AND d.obj# = chl.dimobj#
AND h.hierid# = chl.hierid#
AND chl.levelid# = cdl.levelid#
AND d.obj# = cdl.dimobj#
AND phl.pos# = chl.pos# + 1
AND ch.dimension_irid = d.obj#
AND ch.name = h.hiername
union all
select owner, dimension_name, hierarchy_name, parent_level_name,
       child_level_name, position
from olapsys.EXPORT2_dim_hier_level_uses
with read only
/
show errors;


create or replace view olapsys.EXPORT2_join_key_column_uses as select
  d.owner owner,
  d.name dimension_name,
--  (case when h.hidden = 'Y'
--        then null else h.name end) hierarchy_name,
  h.name hierarchy_name,
  l.name child_level_name,
  u.username table_owner,
  o.name table_name,
  ck.name key_column_name,
  cf.name foreign_key_column_name,
  dhlm.position position,
  null join_key_type /* join key type */
from olapsys.CwM2$dimension d,
     olapsys.CwM2$hierarchy h,
     olapsys.CwM2$level l,
     olapsys.CwM2$HierLevelRel hlr,
     olapsys.CwM2$DimHierLvlMap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ ck,
     sys.col$ cf
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.DimHierLvl_IRID = hlr.irid and
      dhlm.object_ID = o.obj# and
      dhlm.column_id = ck.col# and
      dhlm.object_ID = ck.obj# and
      dhlm.parentcolumn_id = cf.col#(+) and
      dhlm.object_ID = cf.obj#(+) and
      o.owner# = u.user_id 
--    dhlm.style = 'STAR'
UNION     
select d.owner owner,
  d.name dimension_name,
--  (case when h.hidden = 'Y'
--        then null else h.name end) hierarchy_name,
  h.name hierarchy_name,
  l.name child_level_name,
  u.username table_owner,
  o.name table_name,
  ck.name key_column_name,
  cf.name foreign_key_column_name,
  dhlm.position position,
  null join_key_type /* join key type */
from olapsys.CwM2$dimension d,
     olapsys.CwM2$hierarchy h,
     olapsys.CwM2$level l,
     olapsys.CwM2$HierLevelRel hlr,
     olapsys.CwM2$DimHierLvlMap dhlm,
     dba_users u,
     sys.obj$ o,
     sys.col$ ck,
     sys.col$ cf
where h.dimension_irid = d.irid and
      h.irid = hlr.hierarchy_irid and
      l.irid = hlr.childlevel_irid and
      dhlm.DimHierLvl_IRID = hlr.irid and
      dhlm.object_ID = o.obj# and
      dhlm.column_id = ck.col#(+) and
      dhlm.object_ID = ck.obj#(+) and
      dhlm.parentcolumn_id = cf.col# and
      dhlm.object_ID = cf.obj# and
      o.owner# = u.user_id
--    dhlm.style = 'SNOWFLAKE'
with read only
/
show errors;


create or replace view olapsys.EXPORT2ujoin_key_column_uses as
SELECT
  du.username owner
, d.name dimension_name
, h.hiername hierarchy_name
, l.levelname child_level_name
, tu.username table_owner
, t.name table_name
, c.name key_column_name
, c.name foreign_key_column_name
, k.keypos# position
, 'FOREIGN KEY' join_key_type
FROM
  dba_users du
, sys.obj$ d
, sys.hier$ h
, sys.hierlevel$ hl
, sys.dimlevel$ l
, sys.dimjoinkey$ k
, dba_users tu
, sys.obj$ t
, sys.col$ c
, olapsys.cwm$level lev
WHERE du.user_id = d.owner#
AND d.type# = 43
AND d.obj# = h.dimobj#
AND h.dimobj# = hl.dimobj#
AND h.hierid# = hl.hierid#
AND hl.dimobj# = l.dimobj#
AND hl.levelid# = l.levelid#
AND hl.dimobj# = k.dimobj#
AND hl.hierid# = k.hierid#
AND hl.joinkeyid# = k.joinkeyid#
AND k.detailobj# = t.obj#
AND k.col# = c.col#
AND t.obj# = c.obj#
AND t.owner# = tu.user_id
AND lev.dimension_irid = l.dimobj#
AND lev.physicalname = l.levelname
UNION ALL SELECT
  du.username owner
, d.name dimension_name
, h.hiername hierarchy_name
, l.levelname child_level_name
, tu.username table_owner
, t.name table_name
, c.name key_column_name
, c.name foreign_key_column_name
, k.keypos# position
, 'KEY' join_key_type
FROM
  dba_users du
, sys.obj$ d
, sys.hier$ h
, sys.hierlevel$ hl
, sys.dimlevel$ l
, sys.dimlevelkey$ k
, dba_users tu
, sys.obj$ t
, sys.col$ c
, olapsys.cwm$level lev
WHERE du.user_id = d.owner#
AND d.type# = 43
AND d.obj# = h.dimobj#
AND h.dimobj# = hl.dimobj#
AND h.hierid# = hl.hierid#
AND hl.dimobj# = l.dimobj#
AND hl.levelid# = l.levelid#
AND hl.joinkeyid# = 0
AND l.dimobj# = k.dimobj#
AND l.levelid# = k.levelid#
AND k.detailobj# = t.obj#
AND k.col# = c.col#
AND t.obj# = c.obj#
AND t.owner# = tu.user_id
AND lev.dimension_irid = l.dimobj#
AND lev.physicalname = l.levelname
union all
select owner, dimension_name, hierarchy_name, child_level_name, table_owner,
       table_name, key_column_name, foreign_key_column_name, position, join_key_type
from olapsys.EXPORT2_join_key_column_uses
with read only
/
show errors;

-- shoud be same as olapsys.dba$olap_catalogs except as noted
CREATE OR REPLACE VIEW EXPORT_catalogs AS SELECT 
  c.irid catalog_id
, c.name catalog_name
, cp.irid parent_catalog_id
, c.description description
, ce.name entry_name -- added for export
FROM
  olapsys.cwm$classification c
, olapsys.cwm$classification cp
, olapsys.cwm$classificationtype cty
, olapsys.cwm$classificationentry ce
WHERE cty.irid = c.classificationtype_irid
AND cty.name = 'ORACLE_OLAP_CATALOG'
AND c.irid = ce.element_irid
AND ce.name = 'CATALOG'    
AND ce.classification_irid = cp.irid 
UNION ALL SELECT
  c.irid catalog_id
, c.name catalog_name
, null parent_catalog_id
, c.description description
, 'CATALOG' entry_name -- added
FROM
  olapsys.cwm$classification c
, olapsys.cwm$classificationtype cty
WHERE cty.irid = c.classificationtype_irid
AND cty.name = 'ORACLE_OLAP_CATALOG'
AND NOT EXISTS
    (SELECT null 
    FROM olapsys.cwm$classificationentry ce
    WHERE ce.name = 'CATALOG'
    AND ce.element_irid = c.irid)  
WITH READ ONLY
/
show errors;


-- shoud be same as olapsys.dba$olap2_catalogs except as noted
create or replace view olapsys.EXPORT2_catalogs as select
 c.irid catalog_id,
 c.name catalog_name,
 cp.irid parent_catalog_id,
 c.description description,
 ce.name entry_name -- added for export
from
 olapsys.cwm$classification c,
 olapsys.cwm$classification cp,
 olapsys.cwm$classificationtype cty,
 olapsys.cwm$classificationentry ce
where cty.irid = c.classificationtype_irid and
      cty.name = 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.element_irid and
      ce.name = 'CATALOG2' and
      ce.classification_irid = cp.irid
UNION ALL select
 c.irid catalog_id,
 c.name catalog_name,
 null parent_catalog_id,
 c.description description, 
 'CATALOG2' entry_name -- added 
from
 olapsys.cwm$classification c,
 olapsys.cwm$classificationtype cty
where cty.irid = c.classificationtype_irid and
      cty.name = 'ORACLE_OLAP2_CATALOG' and
      NOT EXISTS
       (select null from olapsys.cwm$classificationentry ce
        where ce.name = 'CATALOG2' and
              ce.element_irid = c.irid)
with read only
/
show errors;


-- shoud be same as olapsys.dba$olap2ucatalogs except as noted
create or replace view olapsys.EXPORT2ucatalogs as select
  catalog_id,
  catalog_name,
  parent_catalog_id,
  description,
  entry_name -- added for export
from olapsys.EXPORT_catalogs
union all
select catalog_id,
  catalog_name,
  parent_catalog_id,
  description,
  entry_name -- added export
from olapsys.EXPORT2_catalogs
with read only
/
show errors;


create or replace view olapsys.EXPORT2_catalog_entity_uses as select
 ce.classification_irid catalog_id,
 cub.owner entity_owner,
 cub.name entity_name,
 meas.name child_entity_name,
 ce.name entry_name -- added for export
from /* MEASURES */
 olapsys.cwm$classification c,
 olapsys.cwm$classificationentry ce,
 olapsys.cwm$classificationtype cty,
 olapsys.cwm2$measure meas,
 olapsys.cwm2$cube cub
where cty.irid = c.classificationtype_irid and
      cty.name = 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.classification_irid and
      ce.name = 'MEASURE2' and
      ce.element_irid = meas.irid and
      meas.cube_irid = cub.irid
with read only
/
show errors;


-- shoud be same as olapsys.dba$olap2ucatalog_entity_uses except as noted
create or replace view olapsys.EXPORT2ucatalog_entity_uses as 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
/
show errors;


-- shoud be same as olapsys.dba$olap2_entity_desc_uses except as noted 
create or replace view olapsys.EXPORT2_entity_desc_uses as select
 ce.classification_irid descriptor_id,
 cub.owner entity_owner,
 cub.name entity_name,
 null child_entity_name,
 null secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* CUBE2 */
  cwm$classification c,
  cwm$classificationentry ce,
  cwm$classificationtype cty,
  cwm2$cube cub
where cty.irid = c.classificationtype_irid and
      cty.name <> 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.classification_irid and
      ce.name = 'CUBE2' and
      ce.element_irid = cub.irid
UNION ALL select
 ce.classification_irid descriptor_id,
 cub.owner entity_owner,
 cub.name entity_name,
 meas.name child_entity_name,
 null secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* MEASURE2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm$classificationtype cty,
 cwm2$cube cub,
 cwm2$measure meas
where cty.irid = c.classificationtype_irid and
      cty.name <> 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.classification_irid and
      ce.name = 'MEASURE2' and
      ce.element_irid = meas.irid and
      meas.cube_irid = cub.irid
UNION ALL select
 ce.classification_irid descriptor_id,
 dim.owner entity_owner,
 dim.name entity_name,
 dat.name child_entity_name,
 null secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* DIMENSION ATTRIBUTE2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm$classificationtype cty,
 cwm2$dimension dim,
 cwm2$dimensionattribute dat
where cty.irid = c.classificationtype_irid and
      cty.name <> 'ORACLE_OLAP2_CATALOG' and
      c.irid = ce.classification_irid and
      ce.name = 'DIMENSION ATTRIBUTE2' and
      ce.element_irid = dat.irid and
      dat.dimension_irid = dim.irid
UNION ALL select
 ce.classification_irid descriptor_id,
 dim.owner entity_owner,
 dim.name entity_name,
 lvl.name child_entity_name,
 lat.name secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* LEVEL ATTRIBUTE2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm$classificationtype cty,
 cwm2$dimension dim,
 cwm2$dimensionattribute dat,
 cwm2$levelattribute lat,
 cwm2$level lvl
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.name = 'LEVEL ATTRIBUTE2' and
      ce.element_irid = lat.irid and
      lat.dimattr_irid = dat.irid and
      dat.dimension_irid = dim.irid and
      lvl.irid = lat.level_irid
UNION ALL select
 ce.classification_irid descriptor_id,
 dim.owner entity_owner,
 dim.name entity_name,
 ce.secondary_object_name child_entity_name,
 null secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* DIMENSION2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm$classificationtype cty,
 cwm2$dimension dim
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.name  = 'DIMENSION2' and
      ce.element_irid = dim.irid
UNION ALL select
 ce.classification_irid descriptor_id,
 dim.owner entity_owner,
 dim.name entity_name,
 hier.name child_entity_name,
 null secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* HIERARCHY2 */
 cwm$classification c,
 cwm$classificationentry ce,
 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.name  = 'HIERARCHY2' and
      dim.irid = hier.dimension_irid and
      ce.element_irid = hier.irid
UNION ALL select
 ce.classification_irid descriptor_id,
 dim.owner entity_owner,
 dim.name entity_name,
 lvl.name child_entity_name,
 null secondary_child_entity_name,
 c.name classification_name,  -- added for export
 ce.name entry_name           -- added for export
from /* LEVEL2 */
 cwm$classification c,
 cwm$classificationentry ce,
 cwm$classificationtype cty,
 cwm2$dimension dim,
 cwm2$level lvl
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.name  = 'LEVEL2' and
      dim.irid = lvl.dimension_irid and
      ce.element_irid = lvl.irid
with read only
/
show errors;


-- shoud be same as olapsys.dba$olap2uentity_desc_uses as noted
create or replace view olapsys.EXPORT2uentity_desc_uses as
 select distinct
  ce.classification_irid descriptor_id
, sch.physicalname entity_owner
, cub.physicalname entity_name
, null child_entity_name
, NULL secondary_child_entity_name
, c.name classification_name  -- added for export
, ce.name entry_name          -- added for export
FROM /* CUBE */
  cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$cube cub
, cwm$model sch
, dba_users u
, cwm$cubedimensionuse cdu
, 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 = 'CUBE'
AND ce.element_irid = cub.irid
AND cub.datamodel_irid = sch.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 cd.irid = do.obj#
AND do.type# = 43
AND cd.irid = cdu.abstractdimension_irid
UNION ALL SELECT DISTINCT
  ce.classification_irid descriptor_id
, sch.physicalname entity_owner
, cub.physicalname entity_name
, msr.physicalname child_entity_name
, NULL secondary_child_entity_name
, c.name classification_name  -- added for export
, ce.name entry_name          -- added for export
FROM  /* MEASURE */
  cwm$classification c
, cwm$classificationentry ce
, cwm$classificationtype cty
, cwm$measure msr
, cwm$cube cub
, cwm$model sch
, dba_users u
, cwm$cubedimensionuse cdu
, 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 cub.datamodel_irid = sch.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 cd.irid = do.obj#
AND do.type# = 43
AND cd.irid = cdu.abstractdimension_irid
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
, c.name classification_name  -- added for export
, ce.name entry_name          -- added for export
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
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
, c.name classification_name  -- added for export
, ce.name entry_name          -- added for export
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
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
, c.name classification_name  -- added for export
, ce.name entry_name          -- added for export
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
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
, c.name classification_name  -- added for export
, ce.name entry_name          -- added for export
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
union all
select descriptor_id,
 entity_owner,
 entity_name,
 child_entity_name,
 secondary_child_entity_name,
 classification_name,  -- added for export
 entry_name            -- added for export
from olapsys.EXPORT2_entity_desc_uses
with read only
/
show errors;