[Home] [Help]
SELECT
u.name OWNER,
o.name CUBE_NAME,
c.obj# CUBE_ID,
a.awname AW_NAME,
syn.syntax_clob CONSISTENT_SOLVE_SPEC,
d.description_value DESCRIPTION,
io.option_value SPARSE_TYPE,
syn2.syntax_clob PRECOMPUTE_CONDITION,
io2.option_num_value PRECOMPUTE_PERCENT,
io3.option_num_value PRECOMPUTE_PERCENT_TOP,
od.name PARTITION_DIMENSION_NAME,
h.hierarchy_name PARTITION_HIERARCHY_NAME,
dl.level_name PARTITION_LEVEL_NAME,
io5.option_value REFRESH_MVIEW_NAME,
io6.option_value REWRITE_MVIEW_NAME,
syn3.syntax_clob DEFAULT_BUILD_SPEC,
io7.option_value MEASURE_STORAGE,
syn4.syntax_clob SQL_CUBE_STORAGE_TYPE,
io8.option_value CUBE_STORAGE_TYPE
FROM
olap_cubes$ c,
user$ u,
aw$ a,
obj$ o,
olap_syntax$ syn,
olap_syntax$ syn2,
olap_syntax$ syn3,
olap_syntax$ syn4,
(select d.* from olap_descriptions$ d, nls_session_parameters n where
n.parameter = 'NLS_LANGUAGE'
and d.description_type = 'Description'
and d.owning_object_type = 1 --CUBE
and (d.language = n.value
or d.language like n.value || '\_%' escape '\')) d,
olap_impl_options$ io,
olap_impl_options$ io2,
olap_impl_options$ io3,
olap_impl_options$ io4,
olap_impl_options$ io5,
olap_impl_options$ io6,
olap_impl_options$ io7,
olap_impl_options$ io8,
olap_hier_levels$ hl,
olap_dim_levels$ dl,
olap_hierarchies$ h,
obj$ od
WHERE
o.obj#=c.obj#
AND o.owner#=u.user#
AND c.awseq#=a.awseq#(+)
AND c.obj#=d.owning_object_id(+)
AND syn.owner_id(+)=c.obj#
AND syn.owner_type(+)=1
AND syn.ref_role(+)=16 -- consistent solve spec
AND syn2.owner_id(+)=c.obj#
AND syn2.owner_type(+)=1
AND syn2.ref_role(+)=20 -- precompute condition
AND syn3.owner_id(+)=c.obj#
AND syn3.owner_type(+)=1
AND syn3.ref_role(+)=17 -- default build spec
AND syn4.owner_id(+)=c.obj#
AND syn4.owner_type(+)=1
AND syn4.ref_role(+)=24 -- sql cube storage type
AND io.owning_objectid(+)=c.obj#
AND io.object_type(+)=1
AND io.option_type(+)=7 -- sparse type
AND io2.owning_objectid(+)=c.obj#
AND io2.object_type(+)=1
AND io2.option_type(+)=24 -- precompute percent
AND io3.owning_objectid(+)=c.obj#
AND io3.object_type(+)=1
AND io3.option_type(+)=25 -- precompute percent top
AND io4.owning_objectid(+)=c.obj#
AND io4.object_type(+)=1
AND io4.option_type(+)=9 -- partition level
AND io4.option_num_value=hl.hierarchy_level_id(+)
AND io5.owning_objectid(+)=c.obj#
AND io5.object_type(+)=1
AND io5.option_type(+)=30 -- refresh MV name
AND io6.owning_objectid(+)=c.obj#
AND io6.object_type(+)=1
AND io6.option_type(+)=31 -- rewrite MV name
AND io7.owning_objectid(+)=c.obj#
AND io7.object_type(+)=1
AND io7.option_type(+)=17 -- measure storage
AND io8.owning_objectid(+)=c.obj#
AND io8.object_type(+)=1
AND io8.option_type(+)=20 -- cube storage type
AND hl.hierarchy_id=h.hierarchy_id(+)
AND hl.dim_level_id=dl.level_id(+)
AND h.dim_obj#=od.obj#(+)
SELECT
U.NAME OWNER
,
O.NAME CUBE_NAME
,
C.OBJ# CUBE_ID
,
A.AWNAME AW_NAME
,
SYN.SYNTAX_CLOB CONSISTENT_SOLVE_SPEC
,
D.DESCRIPTION_VALUE DESCRIPTION
,
IO.OPTION_VALUE SPARSE_TYPE
,
SYN2.SYNTAX_CLOB PRECOMPUTE_CONDITION
,
IO2.OPTION_NUM_VALUE PRECOMPUTE_PERCENT
,
IO3.OPTION_NUM_VALUE PRECOMPUTE_PERCENT_TOP
,
OD.NAME PARTITION_DIMENSION_NAME
,
H.HIERARCHY_NAME PARTITION_HIERARCHY_NAME
,
DL.LEVEL_NAME PARTITION_LEVEL_NAME
,
IO5.OPTION_VALUE REFRESH_MVIEW_NAME
,
IO6.OPTION_VALUE REWRITE_MVIEW_NAME
,
SYN3.SYNTAX_CLOB DEFAULT_BUILD_SPEC
,
IO7.OPTION_VALUE MEASURE_STORAGE
,
SYN4.SYNTAX_CLOB SQL_CUBE_STORAGE_TYPE
,
IO8.OPTION_VALUE CUBE_STORAGE_TYPE
FROM
OLAP_CUBES$ C
,
USER$ U
,
AW$ A
,
OBJ$ O
,
OLAP_SYNTAX$ SYN
,
OLAP_SYNTAX$ SYN2
,
OLAP_SYNTAX$ SYN3
,
OLAP_SYNTAX$ SYN4
,
(SELECT D.*
FROM OLAP_DESCRIPTIONS$ D
, NLS_SESSION_PARAMETERS N WHERE
N.PARAMETER = 'NLS_LANGUAGE'
AND D.DESCRIPTION_TYPE = 'DESCRIPTION'
AND D.OWNING_OBJECT_TYPE = 1 --CUBE
AND (D.LANGUAGE = N.VALUE
OR D.LANGUAGE LIKE N.VALUE || '\_%' ESCAPE '\')) D
,
OLAP_IMPL_OPTIONS$ IO
,
OLAP_IMPL_OPTIONS$ IO2
,
OLAP_IMPL_OPTIONS$ IO3
,
OLAP_IMPL_OPTIONS$ IO4
,
OLAP_IMPL_OPTIONS$ IO5
,
OLAP_IMPL_OPTIONS$ IO6
,
OLAP_IMPL_OPTIONS$ IO7
,
OLAP_IMPL_OPTIONS$ IO8
,
OLAP_HIER_LEVELS$ HL
,
OLAP_DIM_LEVELS$ DL
,
OLAP_HIERARCHIES$ H
,
OBJ$ OD
WHERE
O.OBJ#=C.OBJ#
AND O.OWNER#=U.USER#
AND C.AWSEQ#=A.AWSEQ#(+)
AND C.OBJ#=D.OWNING_OBJECT_ID(+)
AND SYN.OWNER_ID(+)=C.OBJ#
AND SYN.OWNER_TYPE(+)=1
AND SYN.REF_ROLE(+)=16 -- CONSISTENT SOLVE SPEC
AND SYN2.OWNER_ID(+)=C.OBJ#
AND SYN2.OWNER_TYPE(+)=1
AND SYN2.REF_ROLE(+)=20 -- PRECOMPUTE CONDITION
AND SYN3.OWNER_ID(+)=C.OBJ#
AND SYN3.OWNER_TYPE(+)=1
AND SYN3.REF_ROLE(+)=17 -- DEFAULT BUILD SPEC
AND SYN4.OWNER_ID(+)=C.OBJ#
AND SYN4.OWNER_TYPE(+)=1
AND SYN4.REF_ROLE(+)=24 -- SQL CUBE STORAGE TYPE
AND IO.OWNING_OBJECTID(+)=C.OBJ#
AND IO.OBJECT_TYPE(+)=1
AND IO.OPTION_TYPE(+)=7 -- SPARSE TYPE
AND IO2.OWNING_OBJECTID(+)=C.OBJ#
AND IO2.OBJECT_TYPE(+)=1
AND IO2.OPTION_TYPE(+)=24 -- PRECOMPUTE PERCENT
AND IO3.OWNING_OBJECTID(+)=C.OBJ#
AND IO3.OBJECT_TYPE(+)=1
AND IO3.OPTION_TYPE(+)=25 -- PRECOMPUTE PERCENT TOP
AND IO4.OWNING_OBJECTID(+)=C.OBJ#
AND IO4.OBJECT_TYPE(+)=1
AND IO4.OPTION_TYPE(+)=9 -- PARTITION LEVEL
AND IO4.OPTION_NUM_VALUE=HL.HIERARCHY_LEVEL_ID(+)
AND IO5.OWNING_OBJECTID(+)=C.OBJ#
AND IO5.OBJECT_TYPE(+)=1
AND IO5.OPTION_TYPE(+)=30 -- REFRESH MV NAME
AND IO6.OWNING_OBJECTID(+)=C.OBJ#
AND IO6.OBJECT_TYPE(+)=1
AND IO6.OPTION_TYPE(+)=31 -- REWRITE MV NAME
AND IO7.OWNING_OBJECTID(+)=C.OBJ#
AND IO7.OBJECT_TYPE(+)=1
AND IO7.OPTION_TYPE(+)=17 -- MEASURE STORAGE
AND IO8.OWNING_OBJECTID(+)=C.OBJ#
AND IO8.OBJECT_TYPE(+)=1
AND IO8.OPTION_TYPE(+)=20 -- CUBE STORAGE TYPE
AND HL.HIERARCHY_ID=H.HIERARCHY_ID(+)
AND HL.DIM_LEVEL_ID=DL.LEVEL_ID(+)
AND H.DIM_OBJ#=OD.OBJ#(+)
|
|
|
|