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.

VIEW: SYS.USER_CUBE_SUB_PARTITION_LEVELS

Source

View Text - Preformatted


SELECT
  o.name CUBE_NAME,
  io.option_value SUB_PARTITION_LEVEL_NAME,
  io2.option_num_value PRECOMPUTE_PERCENT,
  od.name PARTITION_DIMENSION_NAME,
  h.hierarchy_name PARTITION_HIERARCHY_NAME,
  dl.level_name PARTITION_LEVEL_NAME,
  1 SUB_PARTITION_LEVEL_ORDER
FROM
  olap_cubes$ c,
  obj$ o,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  olap_hier_levels$ hl,
  olap_dim_levels$ dl,
  olap_hierarchies$ h,
  obj$ od
WHERE
  o.obj#=c.obj#
  AND o.owner#=USERENV('SCHEMAID')
  AND io.owning_objectid(+)=c.obj#
  AND io.object_type(+)=1 -- CUBE
  AND io.option_type(+) = 38 -- sub partition1 name
  AND io2.owning_objectid(+)=c.obj#
  AND io2.object_type(+)=1 -- CUBE
  AND io2.option_type(+) = 39 -- sub partition1 precompute percent
  AND io3.owning_objectid(+)=c.obj#
  AND io3.object_type(+)=1 --CUBE
  AND io3.option_type(+) = 37 -- sub partition level1
  AND io3.option_num_value=hl.hierarchy_level_id(+)
  AND hl.hierarchy_id=h.hierarchy_id
  AND hl.dim_level_id=dl.level_id
  AND h.dim_obj#=od.obj#
UNION ALL
SELECT
  o.name CUBE_NAME,
  io.option_value SUB_PARTITION_LEVEL_NAME,
  io2.option_num_value PRECOMPUTE_PERCENT,
  od.name PARTITION_DIMENSION_NAME,
  h.hierarchy_name PARTITION_HIERARCHY_NAME,
  dl.level_name PARTITION_LEVEL_NAME,
  2 SUB_PARTITION_LEVEL_ORDER
FROM
  olap_cubes$ c,
  obj$ o,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  olap_hier_levels$ hl,
  olap_dim_levels$ dl,
  olap_hierarchies$ h,
  obj$ od
WHERE
  o.obj#=c.obj#
  AND o.owner#=USERENV('SCHEMAID')
  AND io.owning_objectid(+)=c.obj#
  AND io.object_type(+)=1 -- CUBE
  AND io.option_type(+) = 41 -- sub partition2 name
  AND io2.owning_objectid(+)=c.obj#
  AND io2.object_type(+)=1 -- CUBE
  AND io2.option_type(+) = 42 -- sub partition2 precompute percent
  AND io3.owning_objectid(+)=c.obj#
  AND io3.object_type(+)=1 --CUBE
  AND io3.option_type(+) = 40 -- sub partition level2
  AND io3.option_num_value=hl.hierarchy_level_id(+)
  AND hl.hierarchy_id=h.hierarchy_id
  AND hl.dim_level_id=dl.level_id
  AND h.dim_obj#=od.obj#
UNION ALL
SELECT
  o.name CUBE_NAME,
  io.option_value SUB_PARTITION_LEVEL_NAME,
  io2.option_num_value PRECOMPUTE_PERCENT,
  od.name PARTITION_DIMENSION_NAME,
  h.hierarchy_name PARTITION_HIERARCHY_NAME,
  dl.level_name PARTITION_LEVEL_NAME,
  3 SUB_PARTITION_LEVEL_ORDER
FROM
  olap_cubes$ c,
  obj$ o,
  olap_impl_options$ io,
  olap_impl_options$ io2,
  olap_impl_options$ io3,
  olap_hier_levels$ hl,
  olap_dim_levels$ dl,
  olap_hierarchies$ h,
  obj$ od
WHERE
  o.obj#=c.obj#
  AND o.owner#=USERENV('SCHEMAID')
  AND io.owning_objectid(+)=c.obj#
  AND io.object_type(+)=1 -- CUBE
  AND io.option_type(+) = 44 -- sub partition3 name
  AND io2.owning_objectid(+)=c.obj#
  AND io2.object_type(+)=1 -- CUBE
  AND io2.option_type(+) = 45 -- sub partition3 precompute percent
  AND io3.owning_objectid(+)=c.obj#
  AND io3.object_type(+)=1 --CUBE
  AND io3.option_type(+) = 43 -- sub partition level3
  AND io3.option_num_value=hl.hierarchy_level_id(+)
  AND hl.hierarchy_id=h.hierarchy_id
  AND hl.dim_level_id=dl.level_id
  AND h.dim_obj#=od.obj#
UNION ALL
SELECT
  o.name CUBE_NAME,
  mo.option_value SUB_PARTITION_LEVEL_NAME,
  mo2.option_num_value PRECOMPUTE_PERCENT,
  od.name PARTITION_DIMENSION_NAME,
  h.hierarchy_name PARTITION_HIERARCHY_NAME,
  dl.level_name PARTITION_LEVEL_NAME,
  mo.OPTION_ORDER + 1 SUB_PARTITION_LEVEL_ORDER
FROM
  olap_cubes$ c,
  obj$ o,
  olap_multi_options$ mo,
  olap_multi_options$ mo2,
  olap_multi_options$ mo3,
  olap_hier_levels$ hl,
  olap_dim_levels$ dl,
  olap_hierarchies$ h,
  obj$ od
WHERE
  o.obj#=c.obj#
  AND o.owner#=USERENV('SCHEMAID')
  AND mo.owning_objectid(+)=c.obj#
  AND mo.object_type(+)=1 -- CUBE
  AND mo.option_type(+) = 5 -- sub partition name
  AND mo2.owning_objectid(+)=c.obj#
  AND mo2.object_type(+)=1 -- CUBE
  AND mo2.option_type(+) = 6 -- sub partition precompute percent
  AND mo3.owning_objectid(+)=c.obj#
  AND mo3.object_type(+)=1 --CUBE
  AND mo3.option_type(+) = 4 -- sub partition level
  AND mo.OPTION_ORDER = mo2.OPTION_ORDER
  AND mo2.OPTION_ORDER = mo3.OPTION_ORDER
  AND mo3.option_num_value=hl.hierarchy_level_id(+)
  AND hl.hierarchy_id=h.hierarchy_id
  AND hl.dim_level_id=dl.level_id
  AND h.dim_obj#=od.obj#

View Text - HTML Formatted

SELECT O.NAME CUBE_NAME
, IO.OPTION_VALUE SUB_PARTITION_LEVEL_NAME
, IO2.OPTION_NUM_VALUE PRECOMPUTE_PERCENT
, OD.NAME PARTITION_DIMENSION_NAME
, H.HIERARCHY_NAME PARTITION_HIERARCHY_NAME
, DL.LEVEL_NAME PARTITION_LEVEL_NAME
, 1 SUB_PARTITION_LEVEL_ORDER FROM OLAP_CUBES$ C
, OBJ$ O
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ OD WHERE O.OBJ#=C.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND IO.OWNING_OBJECTID(+)=C.OBJ#
AND IO.OBJECT_TYPE(+)=1 -- CUBE
AND IO.OPTION_TYPE(+) = 38 -- SUB PARTITION1 NAME
AND IO2.OWNING_OBJECTID(+)=C.OBJ#
AND IO2.OBJECT_TYPE(+)=1 -- CUBE
AND IO2.OPTION_TYPE(+) = 39 -- SUB PARTITION1 PRECOMPUTE PERCENT
AND IO3.OWNING_OBJECTID(+)=C.OBJ#
AND IO3.OBJECT_TYPE(+)=1 --CUBE
AND IO3.OPTION_TYPE(+) = 37 -- SUB PARTITION LEVEL1
AND IO3.OPTION_NUM_VALUE=HL.HIERARCHY_LEVEL_ID(+)
AND HL.HIERARCHY_ID=H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID=DL.LEVEL_ID
AND H.DIM_OBJ#=OD.OBJ# UNION ALL SELECT O.NAME CUBE_NAME
, IO.OPTION_VALUE SUB_PARTITION_LEVEL_NAME
, IO2.OPTION_NUM_VALUE PRECOMPUTE_PERCENT
, OD.NAME PARTITION_DIMENSION_NAME
, H.HIERARCHY_NAME PARTITION_HIERARCHY_NAME
, DL.LEVEL_NAME PARTITION_LEVEL_NAME
, 2 SUB_PARTITION_LEVEL_ORDER FROM OLAP_CUBES$ C
, OBJ$ O
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ OD WHERE O.OBJ#=C.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND IO.OWNING_OBJECTID(+)=C.OBJ#
AND IO.OBJECT_TYPE(+)=1 -- CUBE
AND IO.OPTION_TYPE(+) = 41 -- SUB PARTITION2 NAME
AND IO2.OWNING_OBJECTID(+)=C.OBJ#
AND IO2.OBJECT_TYPE(+)=1 -- CUBE
AND IO2.OPTION_TYPE(+) = 42 -- SUB PARTITION2 PRECOMPUTE PERCENT
AND IO3.OWNING_OBJECTID(+)=C.OBJ#
AND IO3.OBJECT_TYPE(+)=1 --CUBE
AND IO3.OPTION_TYPE(+) = 40 -- SUB PARTITION LEVEL2
AND IO3.OPTION_NUM_VALUE=HL.HIERARCHY_LEVEL_ID(+)
AND HL.HIERARCHY_ID=H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID=DL.LEVEL_ID
AND H.DIM_OBJ#=OD.OBJ# UNION ALL SELECT O.NAME CUBE_NAME
, IO.OPTION_VALUE SUB_PARTITION_LEVEL_NAME
, IO2.OPTION_NUM_VALUE PRECOMPUTE_PERCENT
, OD.NAME PARTITION_DIMENSION_NAME
, H.HIERARCHY_NAME PARTITION_HIERARCHY_NAME
, DL.LEVEL_NAME PARTITION_LEVEL_NAME
, 3 SUB_PARTITION_LEVEL_ORDER FROM OLAP_CUBES$ C
, OBJ$ O
, OLAP_IMPL_OPTIONS$ IO
, OLAP_IMPL_OPTIONS$ IO2
, OLAP_IMPL_OPTIONS$ IO3
, OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ OD WHERE O.OBJ#=C.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND IO.OWNING_OBJECTID(+)=C.OBJ#
AND IO.OBJECT_TYPE(+)=1 -- CUBE
AND IO.OPTION_TYPE(+) = 44 -- SUB PARTITION3 NAME
AND IO2.OWNING_OBJECTID(+)=C.OBJ#
AND IO2.OBJECT_TYPE(+)=1 -- CUBE
AND IO2.OPTION_TYPE(+) = 45 -- SUB PARTITION3 PRECOMPUTE PERCENT
AND IO3.OWNING_OBJECTID(+)=C.OBJ#
AND IO3.OBJECT_TYPE(+)=1 --CUBE
AND IO3.OPTION_TYPE(+) = 43 -- SUB PARTITION LEVEL3
AND IO3.OPTION_NUM_VALUE=HL.HIERARCHY_LEVEL_ID(+)
AND HL.HIERARCHY_ID=H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID=DL.LEVEL_ID
AND H.DIM_OBJ#=OD.OBJ# UNION ALL SELECT O.NAME CUBE_NAME
, MO.OPTION_VALUE SUB_PARTITION_LEVEL_NAME
, MO2.OPTION_NUM_VALUE PRECOMPUTE_PERCENT
, OD.NAME PARTITION_DIMENSION_NAME
, H.HIERARCHY_NAME PARTITION_HIERARCHY_NAME
, DL.LEVEL_NAME PARTITION_LEVEL_NAME
, MO.OPTION_ORDER + 1 SUB_PARTITION_LEVEL_ORDER FROM OLAP_CUBES$ C
, OBJ$ O
, OLAP_MULTI_OPTIONS$ MO
, OLAP_MULTI_OPTIONS$ MO2
, OLAP_MULTI_OPTIONS$ MO3
, OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ OD WHERE O.OBJ#=C.OBJ#
AND O.OWNER#=USERENV('SCHEMAID')
AND MO.OWNING_OBJECTID(+)=C.OBJ#
AND MO.OBJECT_TYPE(+)=1 -- CUBE
AND MO.OPTION_TYPE(+) = 5 -- SUB PARTITION NAME
AND MO2.OWNING_OBJECTID(+)=C.OBJ#
AND MO2.OBJECT_TYPE(+)=1 -- CUBE
AND MO2.OPTION_TYPE(+) = 6 -- SUB PARTITION PRECOMPUTE PERCENT
AND MO3.OWNING_OBJECTID(+)=C.OBJ#
AND MO3.OBJECT_TYPE(+)=1 --CUBE
AND MO3.OPTION_TYPE(+) = 4 -- SUB PARTITION LEVEL
AND MO.OPTION_ORDER = MO2.OPTION_ORDER
AND MO2.OPTION_ORDER = MO3.OPTION_ORDER
AND MO3.OPTION_NUM_VALUE=HL.HIERARCHY_LEVEL_ID(+)
AND HL.HIERARCHY_ID=H.HIERARCHY_ID
AND HL.DIM_LEVEL_ID=DL.LEVEL_ID
AND H.DIM_OBJ#=OD.OBJ#