eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

VIEW: SYS.ALL_METADATA_PROPERTIES

Source

View Text - Preformatted


SELECT
  u.name OWNER,
  mp.owning_object_id OWNING_OBJECT_ID,
  decode(mp.owning_type, '1', 'CUBE',
                         '2', 'MEASURE',
                         '3', 'MODEL',
                         '4', 'ASSIGNMENT',
                         '6', 'CALCULATION MEMBER',
                         '8', 'BUILD PROCESS',
                         '10', 'MEASURE FOLDER',
                         '11', 'DIMENSION',
                         '12', 'DIMENSION LEVEL',
                         '13', 'HIERARCHY',
                         '14', 'HIERARCHY LEVEL',
                         '15', 'ATTRIBUTE',
                         '16', 'DIMENSIONALITY',
                         '17', 'ATTRIBUTE MAP',
                         '18', 'HIER LEVEL MAP',
                         '19', 'SOLVED LEVEL HIER MAP',
                         '20', 'SOLVED VALUE HIER MAP',
                         '21', 'MEMBER LIST MAP',
                         '22', 'CUBE MAP',
                         '23', 'CUBE DIMENSIONALITY MAP',
                         '24', 'MEASURE MAP',
                         '34', 'METADATA PROPERTY') OWNING_TYPE,
  mp.property_id PROPERTY_ID,
  mp.property_key PROPERTY_KEY,
  mp.property_value PROPERTY_VALUE,
  mp.property_order PROPERTY_ORDER
FROM
  user$ u,
  obj$ o,
  olap_metadata_properties$ mp
WHERE
  o.obj# = mp.top_obj# -- joined via the top level object id
  AND o.type# = 92 -- Cube Dimension
  AND o.owner#=u.user#(+)
  AND mp.owning_type2 IS NULL
  AND (o.owner# in (userenv('SCHEMAID'), 1)   -- public objects
       or o.obj# in
            ( select obj#  -- directly granted privileges
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or   -- user has system privileges
              ( exists (select null from v$enabledprivs
                        where priv_number in (-302, -- ALTER ANY PRIMARY DIMENSION
                                              -304, -- DELETE ANY PRIMARY DIMENSION
                                              -305, -- DROP ANY PRIMARY DIMENSION
                                              -306, -- INSERT ANY PRIMARY DIMENSION
                                              -307) -- SELECT ANY PRIMARY DIMENSION
                        )
              )
            )
UNION ALL
SELECT
  u.name OWNER,
  mp.owning_object_id OWNING_OBJECT_ID,
  decode(mp.owning_type, '1', 'CUBE',
                         '2', 'MEASURE',
                         '3', 'MODEL',
                         '4', 'ASSIGNMENT',
                         '6', 'CALCULATION MEMBER',
                         '8', 'BUILD PROCESS',
                         '10', 'MEASURE FOLDER',
                         '11', 'DIMENSION',
                         '12', 'DIMENSION LEVEL',
                         '13', 'HIERARCHY',
                         '14', 'HIERARCHY LEVEL',
                         '15', 'ATTRIBUTE',
                         '16', 'DIMENSIONALITY',
                         '17', 'ATTRIBUTE MAP',
                         '18', 'HIER LEVEL MAP',
                         '19', 'SOLVED LEVEL HIER MAP',
                         '20', 'SOLVED VALUE HIER MAP',
                         '21', 'MEMBER LIST MAP',
                         '22', 'CUBE MAP',
                         '23', 'CUBE DIMENSIONALITY MAP',
                         '24', 'MEASURE MAP',
                         '34', 'METADATA PROPERTY') OWNING_TYPE,
  mp.property_id PROPERTY_ID,
  mp.property_key PROPERTY_KEY,
  mp.property_value PROPERTY_VALUE,
  mp.property_order PROPERTY_ORDER
FROM
  user$ u,
  obj$ o,
  olap_metadata_properties$ mp,
 (SELECT
    obj#,
    MIN(have_dim_access) have_all_dim_access
  FROM
    (SELECT
      c.obj# obj#,
      (CASE
        WHEN
        (do.owner# in (userenv('SCHEMAID'), 1)   -- public objects
         or do.obj# in
              ( select obj#  -- directly granted privileges
                from sys.objauth$
                where grantee# in ( select kzsrorol from x$kzsro )
              )
         or   -- user has system privileges
                ( exists (select null from v$enabledprivs
                          where priv_number in (-302, -- ALTER ANY PRIMARY DIMENSION
                                                -304, -- DELETE ANY PRIMARY DIMENSION
                                                -305, -- DROP ANY PRIMARY DIMENSION
                                                -306, -- INSERT ANY PRIMARY DIMENSION
                                                -307) -- SELECT ANY PRIMARY DIMENSION
                          )
                )
        )
        THEN 1
        ELSE 0
       END) have_dim_access
    FROM
      olap_cubes$ c,
      dependency$ d,
      obj$ do
    WHERE
      do.obj# = d.p_obj#
      AND do.type# = 92 -- CUBE DIMENSION
      AND c.obj# = d.d_obj#
    )
    GROUP BY obj# ) da
WHERE
  o.obj# = mp.top_obj# -- joined via the top level object id
  AND o.type# = 93 -- Cube
  AND o.obj#=da.obj#(+)
  AND o.owner#=u.user#(+)
  AND mp.owning_type2 IS NULL
  AND (o.owner# in (userenv('SCHEMAID'), 1)   -- public objects
       or o.obj# in
            ( select obj#  -- directly granted privileges
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or   -- user has system privileges
              ( exists (select null from v$enabledprivs
                        where priv_number in (-309, -- ALTER ANY CUBE
                                              -311, -- DROP ANY CUBE
                                              -312, -- SELECT ANY CUBE
                                              -313) -- UPDATE ANY CUBE
                        )
              )
            )
  AND ((have_all_dim_access = 1) OR (have_all_dim_access is NULL))
UNION ALL
SELECT
  u.name OWNER,
  mp.owning_object_id OWNING_OBJECT_ID,
  decode(mp.owning_type, '1', 'CUBE',
                         '2', 'MEASURE',
                         '3', 'MODEL',
                         '4', 'ASSIGNMENT',
                         '6', 'CALCULATION MEMBER',
                         '8', 'BUILD PROCESS',
                         '10', 'MEASURE FOLDER',
                         '11', 'DIMENSION',
                         '12', 'DIMENSION LEVEL',
                         '13', 'HIERARCHY',
                         '14', 'HIERARCHY LEVEL',
                         '15', 'ATTRIBUTE',
                         '16', 'DIMENSIONALITY',
                         '17', 'ATTRIBUTE MAP',
                         '18', 'HIER LEVEL MAP',
                         '19', 'SOLVED LEVEL HIER MAP',
                         '20', 'SOLVED VALUE HIER MAP',
                         '21', 'MEMBER LIST MAP',
                         '22', 'CUBE MAP',
                         '23', 'CUBE DIMENSIONALITY MAP',
                         '24', 'MEASURE MAP',
                         '34', 'METADATA PROPERTY') OWNING_TYPE,
  mp.property_id PROPERTY_ID,
  mp.property_key PROPERTY_KEY,
  mp.property_value PROPERTY_VALUE,
  mp.property_order PROPERTY_ORDER
FROM
  user$ u,
  obj$ o,
  olap_metadata_properties$ mp
WHERE
  o.obj# = mp.top_obj# -- joined via the top level object id
  AND o.type# = 94 -- Measure Folder
  AND o.owner#=u.user#(+)
  AND mp.owning_type2 IS NULL
  AND (o.owner# in (userenv('SCHEMAID'), 1)   -- public objects
       or o.obj# in
            ( select obj#  -- directly granted privileges
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or   -- user has system privileges
              ( exists (select null from v$enabledprivs
                        where priv_number in (-316, -- DELETE ANY MEASURE FOLDER
                                              -317, -- DROP ANY MEASURE FOLDER
                                              -318) -- INSERT ANY MEASURE FOLDER
                        )
              )
       or   -- user has access to cubes in measure folder
              ( exists (select null from olap_meas_folder_contents$ mfc, olap_measures$ m
                        where mfc.measure_folder_obj# = o.obj#
                          and m.measure_id  = mfc.object_id
                          and (
                              m.cube_obj# in
                                ( select obj#  -- directly granted authorization
                                  from sys.objauth$
                                  where grantee# in ( select kzsrorol from x$kzsro )
                                )
                              )
                       )
              )
            )
UNION ALL
SELECT
  u.name OWNER,
  mp.owning_object_id OWNING_OBJECT_ID,
  decode(mp.owning_type, '1', 'CUBE',
                         '2', 'MEASURE',
                         '3', 'MODEL',
                         '4', 'ASSIGNMENT',
                         '6', 'CALCULATION MEMBER',
                         '8', 'BUILD PROCESS',
                         '10', 'MEASURE FOLDER',
                         '11', 'DIMENSION',
                         '12', 'DIMENSION LEVEL',
                         '13', 'HIERARCHY',
                         '14', 'HIERARCHY LEVEL',
                         '15', 'ATTRIBUTE',
                         '16', 'DIMENSIONALITY',
                         '17', 'ATTRIBUTE MAP',
                         '18', 'HIER LEVEL MAP',
                         '19', 'SOLVED LEVEL HIER MAP',
                         '20', 'SOLVED VALUE HIER MAP',
                         '21', 'MEMBER LIST MAP',
                         '22', 'CUBE MAP',
                         '23', 'CUBE DIMENSIONALITY MAP',
                         '24', 'MEASURE MAP',
                         '34', 'METADATA PROPERTY') OWNING_TYPE,
  mp.property_id PROPERTY_ID,
  mp.property_key PROPERTY_KEY,
  mp.property_value PROPERTY_VALUE,
  mp.property_order PROPERTY_ORDER
FROM
  user$ u,
  obj$ o,
  olap_metadata_properties$ mp
WHERE
  o.obj# = mp.top_obj# -- joined via the top level object id
  AND o.type# = 95 -- Build Process
  AND o.owner#=u.user#(+)
  AND mp.owning_type2 IS NULL
  AND (o.owner# in (userenv('SCHEMAID'), 1)   -- public objects
       or o.obj# in
            ( select obj#  -- directly granted privileges
              from sys.objauth$
              where grantee# in ( select kzsrorol from x$kzsro )
            )
       or   -- user has system privileges
              ( exists (select null from v$enabledprivs
                        where priv_number in (-321, -- DROP ANY BUILD PROCESS
                                              -322) -- UPDATE ANY BUILD PROCESS
                        )
              )
            )

View Text - HTML Formatted

SELECT U.NAME OWNER
, MP.OWNING_OBJECT_ID OWNING_OBJECT_ID
, DECODE(MP.OWNING_TYPE
, '1'
, 'CUBE'
, '2'
, 'MEASURE'
, '3'
, 'MODEL'
, '4'
, 'ASSIGNMENT'
, '6'
, 'CALCULATION MEMBER'
, '8'
, 'BUILD PROCESS'
, '10'
, 'MEASURE FOLDER'
, '11'
, 'DIMENSION'
, '12'
, 'DIMENSION LEVEL'
, '13'
, 'HIERARCHY'
, '14'
, 'HIERARCHY LEVEL'
, '15'
, 'ATTRIBUTE'
, '16'
, 'DIMENSIONALITY'
, '17'
, 'ATTRIBUTE MAP'
, '18'
, 'HIER LEVEL MAP'
, '19'
, 'SOLVED LEVEL HIER MAP'
, '20'
, 'SOLVED VALUE HIER MAP'
, '21'
, 'MEMBER LIST MAP'
, '22'
, 'CUBE MAP'
, '23'
, 'CUBE DIMENSIONALITY MAP'
, '24'
, 'MEASURE MAP'
, '34'
, 'METADATA PROPERTY') OWNING_TYPE
, MP.PROPERTY_ID PROPERTY_ID
, MP.PROPERTY_KEY PROPERTY_KEY
, MP.PROPERTY_VALUE PROPERTY_VALUE
, MP.PROPERTY_ORDER PROPERTY_ORDER FROM USER$ U
, OBJ$ O
, OLAP_METADATA_PROPERTIES$ MP WHERE O.OBJ# = MP.TOP_OBJ# -- JOINED VIA THE TOP LEVEL OBJECT ID
AND O.TYPE# = 92 -- CUBE DIMENSION
AND O.OWNER#=U.USER#(+)
AND MP.OWNING_TYPE2 IS NULL
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR O.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-302
, -- ALTER ANY PRIMARY DIMENSION -304
, -- DELETE ANY PRIMARY DIMENSION -305
, -- DROP ANY PRIMARY DIMENSION -306
, -- INSERT ANY PRIMARY DIMENSION -307) -- SELECT ANY PRIMARY DIMENSION ) ) ) UNION ALL SELECT U.NAME OWNER
, MP.OWNING_OBJECT_ID OWNING_OBJECT_ID
, DECODE(MP.OWNING_TYPE
, '1'
, 'CUBE'
, '2'
, 'MEASURE'
, '3'
, 'MODEL'
, '4'
, 'ASSIGNMENT'
, '6'
, 'CALCULATION MEMBER'
, '8'
, 'BUILD PROCESS'
, '10'
, 'MEASURE FOLDER'
, '11'
, 'DIMENSION'
, '12'
, 'DIMENSION LEVEL'
, '13'
, 'HIERARCHY'
, '14'
, 'HIERARCHY LEVEL'
, '15'
, 'ATTRIBUTE'
, '16'
, 'DIMENSIONALITY'
, '17'
, 'ATTRIBUTE MAP'
, '18'
, 'HIER LEVEL MAP'
, '19'
, 'SOLVED LEVEL HIER MAP'
, '20'
, 'SOLVED VALUE HIER MAP'
, '21'
, 'MEMBER LIST MAP'
, '22'
, 'CUBE MAP'
, '23'
, 'CUBE DIMENSIONALITY MAP'
, '24'
, 'MEASURE MAP'
, '34'
, 'METADATA PROPERTY') OWNING_TYPE
, MP.PROPERTY_ID PROPERTY_ID
, MP.PROPERTY_KEY PROPERTY_KEY
, MP.PROPERTY_VALUE PROPERTY_VALUE
, MP.PROPERTY_ORDER PROPERTY_ORDER FROM USER$ U
, OBJ$ O
, OLAP_METADATA_PROPERTIES$ MP
, (SELECT OBJ#
, MIN(HAVE_DIM_ACCESS) HAVE_ALL_DIM_ACCESS FROM (SELECT C.OBJ# OBJ#
, (CASE WHEN (DO.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR DO.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-302
, -- ALTER ANY PRIMARY DIMENSION -304
, -- DELETE ANY PRIMARY DIMENSION -305
, -- DROP ANY PRIMARY DIMENSION -306
, -- INSERT ANY PRIMARY DIMENSION -307) -- SELECT ANY PRIMARY DIMENSION ) ) ) THEN 1 ELSE 0 END) HAVE_DIM_ACCESS FROM OLAP_CUBES$ C
, DEPENDENCY$ D
, OBJ$ DO WHERE DO.OBJ# = D.P_OBJ#
AND DO.TYPE# = 92 -- CUBE DIMENSION
AND C.OBJ# = D.D_OBJ# ) GROUP BY OBJ# ) DA WHERE O.OBJ# = MP.TOP_OBJ# -- JOINED VIA THE TOP LEVEL OBJECT ID
AND O.TYPE# = 93 -- CUBE
AND O.OBJ#=DA.OBJ#(+)
AND O.OWNER#=U.USER#(+)
AND MP.OWNING_TYPE2 IS NULL
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR O.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-309
, -- ALTER ANY CUBE -311
, -- DROP ANY CUBE -312
, -- SELECT ANY CUBE -313) -- UPDATE ANY CUBE ) ) )
AND ((HAVE_ALL_DIM_ACCESS = 1) OR (HAVE_ALL_DIM_ACCESS IS NULL)) UNION ALL SELECT U.NAME OWNER
, MP.OWNING_OBJECT_ID OWNING_OBJECT_ID
, DECODE(MP.OWNING_TYPE
, '1'
, 'CUBE'
, '2'
, 'MEASURE'
, '3'
, 'MODEL'
, '4'
, 'ASSIGNMENT'
, '6'
, 'CALCULATION MEMBER'
, '8'
, 'BUILD PROCESS'
, '10'
, 'MEASURE FOLDER'
, '11'
, 'DIMENSION'
, '12'
, 'DIMENSION LEVEL'
, '13'
, 'HIERARCHY'
, '14'
, 'HIERARCHY LEVEL'
, '15'
, 'ATTRIBUTE'
, '16'
, 'DIMENSIONALITY'
, '17'
, 'ATTRIBUTE MAP'
, '18'
, 'HIER LEVEL MAP'
, '19'
, 'SOLVED LEVEL HIER MAP'
, '20'
, 'SOLVED VALUE HIER MAP'
, '21'
, 'MEMBER LIST MAP'
, '22'
, 'CUBE MAP'
, '23'
, 'CUBE DIMENSIONALITY MAP'
, '24'
, 'MEASURE MAP'
, '34'
, 'METADATA PROPERTY') OWNING_TYPE
, MP.PROPERTY_ID PROPERTY_ID
, MP.PROPERTY_KEY PROPERTY_KEY
, MP.PROPERTY_VALUE PROPERTY_VALUE
, MP.PROPERTY_ORDER PROPERTY_ORDER FROM USER$ U
, OBJ$ O
, OLAP_METADATA_PROPERTIES$ MP WHERE O.OBJ# = MP.TOP_OBJ# -- JOINED VIA THE TOP LEVEL OBJECT ID
AND O.TYPE# = 94 -- MEASURE FOLDER
AND O.OWNER#=U.USER#(+)
AND MP.OWNING_TYPE2 IS NULL
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR O.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-316
, -- DELETE ANY MEASURE FOLDER -317
, -- DROP ANY MEASURE FOLDER -318) -- INSERT ANY MEASURE FOLDER ) ) OR -- USER HAS ACCESS TO CUBES IN MEASURE FOLDER ( EXISTS (SELECT NULL
FROM OLAP_MEAS_FOLDER_CONTENTS$ MFC
, OLAP_MEASURES$ M
WHERE MFC.MEASURE_FOLDER_OBJ# = O.OBJ#
AND M.MEASURE_ID = MFC.OBJECT_ID
AND ( M.CUBE_OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED AUTHORIZATION
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) ) ) ) ) UNION ALL SELECT U.NAME OWNER
, MP.OWNING_OBJECT_ID OWNING_OBJECT_ID
, DECODE(MP.OWNING_TYPE
, '1'
, 'CUBE'
, '2'
, 'MEASURE'
, '3'
, 'MODEL'
, '4'
, 'ASSIGNMENT'
, '6'
, 'CALCULATION MEMBER'
, '8'
, 'BUILD PROCESS'
, '10'
, 'MEASURE FOLDER'
, '11'
, 'DIMENSION'
, '12'
, 'DIMENSION LEVEL'
, '13'
, 'HIERARCHY'
, '14'
, 'HIERARCHY LEVEL'
, '15'
, 'ATTRIBUTE'
, '16'
, 'DIMENSIONALITY'
, '17'
, 'ATTRIBUTE MAP'
, '18'
, 'HIER LEVEL MAP'
, '19'
, 'SOLVED LEVEL HIER MAP'
, '20'
, 'SOLVED VALUE HIER MAP'
, '21'
, 'MEMBER LIST MAP'
, '22'
, 'CUBE MAP'
, '23'
, 'CUBE DIMENSIONALITY MAP'
, '24'
, 'MEASURE MAP'
, '34'
, 'METADATA PROPERTY') OWNING_TYPE
, MP.PROPERTY_ID PROPERTY_ID
, MP.PROPERTY_KEY PROPERTY_KEY
, MP.PROPERTY_VALUE PROPERTY_VALUE
, MP.PROPERTY_ORDER PROPERTY_ORDER FROM USER$ U
, OBJ$ O
, OLAP_METADATA_PROPERTIES$ MP WHERE O.OBJ# = MP.TOP_OBJ# -- JOINED VIA THE TOP LEVEL OBJECT ID
AND O.TYPE# = 95 -- BUILD PROCESS
AND O.OWNER#=U.USER#(+)
AND MP.OWNING_TYPE2 IS NULL
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS OR O.OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED PRIVILEGES
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR -- USER HAS SYSTEM PRIVILEGES ( EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-321
, -- DROP ANY BUILD PROCESS -322) -- UPDATE ANY BUILD PROCESS ) ) )