REM REM Copyright (c) 2002, 2009, Oracle and/or its affiliates. REM All rights reserved. REM REM FILE REM olapodm.pls REM REM DESCRIPTION REM Package Body Implementation of DBMS_ODM REM REM NOTES REM REM REM MODIFIED (MM/DD/YY) REM awesley 12/17/08 - bug 7585366 potential sql injection REM port from 102050 bug 7323910 CreateFactOWB slow, add indexes and reorder some code REM glyon 06/24/08 - bug 7204558: eliminate references to sys.user$ REM awesley 12/14/05 - do not return rdbms dimension in all$olap2udim_hier_level_uses view if it is not a cwm1 dimension REM awesley 08/24/05 - fix problem with selected levels and tuples REM awesley 07/05/05 - port from 10105 REM awesley 03/09/05 - owb enhancements REM awesley 02/15/05 - add partitioning parameter to CreateDimMV_GS and bug fixed REM awesley 12/13/04 - . REM awesley 08/16/04 - rewrote REM awesley 07/28/04 - . REM awesley 06/17/04 - . REM awesley 05/26/04 - reformat for readability REM mrangwa 10/03/03 - . REM mrangwa 10/01/03 - Add version for ODM function REM 02/28/03 mrangwal Fix to Dimensions. REM 01/30/03 mrangwal Add Dimension changes REM 01/20/02 mrangwal created CREATE OR REPLACE PACKAGE DBMS_ODM AUTHID CURRENT_USER AS PROCEDURE CreateDimLevTuple(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ); PROCEDURE CreateDimLevTuple(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Levels_Table_Owner IN varchar2 ,p_Levels_Table_Name IN varchar2 ); -- levels table: CREATE TABLE . of SYS.OlapLevel ... PROCEDURE CreateCubeLevelTuple(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ); PROCEDURE CreateCubeLevelTuple(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Levels_Table_Owner IN varchar2 ,p_Levels_Table_Name IN varchar2 ,p_Tuples_Table_Owner IN varchar2 ,p_Tuples_Table_Name IN varchar2 ); -- tuples table: CREATE TABLE .
of SYS.OlapLevelTuple ... PROCEDURE CreateDimMV_GS(p_Dimension_Owner IN varchar2 ,p_Dimension_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Tablespace_MV IN varchar2 default null ,p_Tablespace_Index IN varchar2 default null ); PROCEDURE CreateDimMV_GS(p_Dimension_Owner IN varchar2 ,p_Dimension_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Partitioning IN boolean ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ); PROCEDURE CreateDimMV_GS(p_Dimension_Owner IN varchar2 ,p_Dimension_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Partitioning IN boolean ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Refresh_Method IN varchar2 -- 'FORCE', 'FAST, 'COMPLETE' ,p_Refresh_On IN varchar2 -- 'DEMAND', 'COMMIT' ,p_Execute IN boolean ); PROCEDURE CreateFactMV_GS(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Partitioning IN boolean ,p_Tablespace_MV IN varchar2 default null ,p_Tablespace_Index IN varchar2 default null ); PROCEDURE CreateFactMV_GS(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Tuples_Table_Owner IN varchar2 ,p_Tuples_Table_Name IN varchar2 ,p_Partitioning IN boolean ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Refresh_Method IN varchar2 -- 'FORCE', 'FAST, 'COMPLETE' ,p_Refresh_On IN varchar2 -- 'DEMAND', 'COMMIT' ,p_Execute IN boolean ); PROCEDURE CreateStdFactMV(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Partitioning IN boolean ,p_Materialization_Level IN varchar2 ,p_Materialization_Pct IN number default null ,p_Tablespace_MV IN varchar2 default null ,p_Tablespace_Index IN varchar2 default null ); PROCEDURE CreateStdFactMV(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Output_File IN varchar2 ,p_Output_Path IN varchar2 ,p_Partitioning IN boolean ,p_Materialization_Level IN varchar2 ,p_Materialization_Pct IN number ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Refresh_Method IN varchar2 -- 'FORCE', 'FAST, 'COMPLETE' ,p_Refresh_On IN varchar2 -- 'DEMAND', 'COMMIT' ,p_Execute IN boolean ); PROCEDURE ODM_VERSION; FUNCTION ODMVERSION return varchar2; PROCEDURE CreateDimOWB(p_Run_ID IN number ,p_Dimension_Owner IN varchar2 ,p_Dimension_Name IN varchar2 ); PROCEDURE CreateDimOWB(p_Run_ID IN number ,p_Dimension_Owner IN varchar2 ,p_Dimension_Name IN varchar2 ,p_Partitioning IN boolean ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Refresh_Method IN varchar2 -- 'FORCE', 'FAST, 'COMPLETE' ,p_Refresh_On IN varchar2 -- 'DEMAND', 'COMMIT' ,p_Execute IN boolean ); PROCEDURE CreateFactOWB(p_Run_ID IN number ,p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ); PROCEDURE CreateFactOWB(p_Run_ID IN number ,p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Tuples_Table_Owner IN varchar2 ,p_Tuples_Table_Name IN varchar2 ,p_Partitioning IN boolean ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Refresh_Method IN varchar2 -- 'FORCE', 'FAST, 'COMPLETE' ,p_Refresh_On IN varchar2 -- 'DEMAND', 'COMMIT' ,p_Execute IN boolean ); PROCEDURE CreateStdFactMVOWB(p_Run_ID IN number ,p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Materialization_Level IN varchar2 ,p_Materialization_Pct IN number default null ); PROCEDURE CreateStdFactMVOWB(p_Run_ID IN number ,p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Partitioning IN boolean ,p_Materialization_Level IN varchar2 ,p_Materialization_Pct IN number ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Refresh_Method IN varchar2 -- 'FORCE', 'FAST, 'COMPLETE' ,p_Refresh_On IN varchar2 -- 'DEMAND', 'COMMIT' ,p_Execute IN boolean ); /* deprecated 04/08/26 PROCEDURE CreateCubeLevelTuple(p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Levels IN SYS.OlapLevels ,p_Number_Of_Level_Tuples IN OUT NUMBER ,p_Olaplevel_Tuples OUT SYS.OlapLevelTuples ); */ /* deprecated 04/08/25 PROCEDURE CreateDimMV_GS(p_Run_ID IN NUMBER ,p_Cube_Owner IN varchar2 ,p_Dimension_Name IN varchar2 ); */ /* deprecated 04/08/25 PROCEDURE CreateFactMV_GS(p_Run_ID IN NUMBER ,p_Cube_Owner IN varchar2 ,p_Cube_Name IN varchar2 ,p_Partitioning IN BOOLEAN ,p_Tablespace_MV IN varchar2 ,p_Tablespace_Index IN varchar2 ,p_Outscript OUT CLOB ,p_Number_Of_Level_Tuples IN NUMBER default null ,p_Level_Tuples IN SYS.OlapLevelTuples default null ); */ END; / show errors; -- this view should be the same as all$olap2_Dim_Hier_Level_Uses except as noted create or replace view olapsys.ODM$olap2_Dim_Hier_Level_Uses as select d.owner owner, d.name dimension_name, -- (case when h.hidden = 'N' -- then h.name else null end) hierarchy_name, nvl(h.name ,'NONE') hierarchy_name, -- ALWAYS RETURN HIERARCHY NAME h.solvedcode, -- ADD SOLVEDCODE -- 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 -- d.irid = pl.dimension_irid and d.irid = cl.dimension_irid and h.dimension_irid = d.irid and hlr.hierarchy_IRID = h.irid and -- hlr.parentlevel_irid = pl.irid and hlr.childlevel_irid = cl.irid and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only; / show errors; grant select on olapsys.ODM$olap2_Dim_Hier_Level_Uses to public; show errors; -- this view is based on all$olap2udim_hier_level_uses but it will return hierarchy and all levels, parent is null at top create or replace view olapsys.ODM$olap2udim_hier_level_uses as SELECT distinct udl.owner , udl.dimension_name , nvl(h.hierarchy_name ,'NONE') hierarchy_name , 'UL' solvedcode -- ADD SOLVEDCODE -- , pl.levelname parent_level_name , udl.child_level_name , h.position FROM (SELECT u.username owner , d.obj# dimobj# , d.name dimension_name , dl.levelid# , dl.levelname child_level_name FROM dba_users u , sys.obj$ d , sys.dimlevel$ dl , olapsys.cwm$level lev -- used to insure that it is a cwm1 dimension WHERE u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) AND d.obj# = dl.dimobj# AND dl.dimobj# = lev.dimension_irid AND dl.levelname = lev.physicalname ) udl -- , (SELECT h.dimobj# , h.hierid# , h.hiername hierarchy_name , hl.pos# position , hl.levelid# FROM sys.hier$ h , sys.hierlevel$ hl , olapsys.cwm$hierarchy ch WHERE h.dimobj# = hl.dimobj# AND h.hierid# = hl.hierid# AND h.dimobj# = ch.dimension_irid AND h.hiername = ch.name ) h WHERE udl.dimobj# = h.dimobj#(+) AND udl.levelid# = h.levelid#(+) -- dba_users u -- , sys.obj$ d -- , sys.hier$ h -- , olapsys.cwm$hierarchy ch -- , (SELECT -- pdl.dimobj# -- , pdl.levelname -- , phl.hierid# -- , phl.pos# -- FROM -- sys.dimlevel$ pdl -- , sys.hierlevel$ phl -- WHERE pdl.dimobj# = phl.dimobj# -- AND pdl.levelid# = phl.levelid# -- ) pl -- , (SELECT -- cdl.dimobj# -- , cdl.levelname -- , chl.hierid# -- , chl.pos# -- FROM -- sys.dimlevel$ cdl -- , sys.hierlevel$ chl -- WHERE cdl.dimobj# = chl.dimobj# -- AND cdl.levelid# = chl.levelid# -- ) cl -- WHERE u.user_id = d.owner# -- AND d.type# = 43 /* DIMENSION */ -- AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' -- OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ -- (SELECT null FROM v$enabledprivs -- WHERE priv_number IN (-47,-215,-216,-217))) -- AND d.obj# = h.dimobj# -- AND d.obj# = ch.dimension_irid -- AND h.hiername = ch.name -- -- AND d.obj# = cl.dimobj# -- AND h.hierid# = cl.hierid# -- -- AND pl.pos#(+) = cl.pos# + 1 -- AND pl.dimobj#(+) = cl.dimobj# -- AND pl.hierid#(+) = cl.hierid# -- union all select owner, dimension_name, hierarchy_name, solvedcode, -- parent_level_name, ADD SOLVEDCODE child_level_name, position from olapsys.ODM$olap2_dim_hier_level_uses -- SEE ABOVE with read only; / show errors; grant select on olapsys.ODM$olap2udim_hier_level_uses to public; show errors; -- this view should be the same as all$olap2_level_key_col_uses except as noted create or replace view olapsys.ODM$olap2_level_key_col_uses as select d.owner owner, d.name dimension_name, -- (case when h.hidden = 'N' -- then h.name else null end) hierarchy_name, nvl(h.name ,'NONE') hierarchy_name, -- ALWAYS RETURN HIERARCHY NAME l.name child_level_name, u.username table_owner, o.name table_name, c.name column_name, dhlm.position position 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$ c 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 = c.col# and o.obj# = c.obj# and o.owner# = u.user_id and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only; / show errors; grant select on olapsys.ODM$olap2_level_key_col_uses to public; show errors; -- this view should be the same as all$olap2ulevel_key_col_uses except as noted create or replace view olapsys.ODM$olap2ulevel_key_col_uses as select distinct lkcu.owner owner, lkcu.dimension_name dimension_name, nvl(dhl.hierarchy_name ,'NONE') hierarchy_name, lkcu.level_name child_level_name, lkcu.table_owner table_owner, lkcu.table_name table_name, lkcu.column_name column_name, lkcu.position position from (select u.username owner, d.name dimension_name, l.levelname level_name, c.name column_name, k.keypos# position, tu.username table_owner, tn.name table_name from dba_users u, sys.obj$ d, sys.dimlevel$ l, sys.dimlevelkey$ k, sys.col$ c, sys.obj$ tn, dba_users tu, olapsys.cwm$level lev where u.user_id = d.owner# and d.type# = 43 and (cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) and d.obj# = l.dimobj# and l.dimobj# = k.dimobj# and l.levelid# = k.levelid# and k.detailobj# = c.obj# and k.col# = c.col# and tn.obj# = c.obj# and tu.user_id = tn.owner# and lev.dimension_irid = l.dimobj# and lev.physicalname = l.levelname ) lkcu, (select u.username dim_owner, o.name dim_name, h.hiername hierarchy_name, dl.levelname level_name from dba_users u, sys.obj$ o, sys.dim$ d, sys.dimlevel$ dl, sys.hier$ h, sys.hierlevel$ hl, olapsys.cwm$level lev where u.user_id = o.owner# and o.type# = 43 and o.obj# = d.obj# and d.obj# = dl.dimobj# and d.obj# = h.dimobj# and h.dimobj# = hl.dimobj# and h.hierid# = hl.hierid# and hl.levelid# = dl.levelid# and lev.dimension_irid = dl.dimobj# and lev.physicalname = dl.levelname ) dhl where lkcu.owner = dhl.dim_owner (+) and lkcu.dimension_name = dhl.dim_name (+) and lkcu.level_name = dhl.level_name (+) UNION ALL select owner, dimension_name, hierarchy_name, child_level_name, table_owner, table_name, column_name, position from olapsys.ODM$olap2_level_key_col_uses with read only / show errors; grant select on olapsys.ODM$olap2ulevel_key_col_uses to public; show errors; -- this view should be the same as all$olap2_join_key_column_uses except as noted create or replace view olapsys.ODM$olap2_join_key_column_uses as select d.owner owner, d.name dimension_name, -- (case when h.hidden = 'N' -- then h.name else null end) hierarchy_name, nvl(h.name ,'NONE') hierarchy_name, -- ALWAYS RETURN HIERARCHY NAME l.name child_level_name, u.username table_owner, o.name table_name, c.name column_name, dhlm.position position, 'KEY' 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$ c 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 = c.col# and o.obj# = c.obj# and o.owner# = u.user_id and dhlm.style = 'STAR' and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) UNION ALL select d.owner owner, d.name dimension_name, -- (case when h.hidden = 'N' -- then h.name else null end) hierarchy_name, nvl(h.name ,'NONE') hierarchy_name, -- ALWAYS RETURN HIERARCHY NAME l.name child_level_name, u.username table_owner, o.name table_name, c.name column_name, dhlm.position position, 'FOREIGN KEY' 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$ c where h.dimension_irid = d.irid and h.irid = hlr.hierarchy_irid and l.irid = hlr.parentlevel_irid and dhlm.DimHierLvl_IRID = hlr.irid and dhlm.object_ID = o.obj# and dhlm.parentcolumn_id = c.col# and o.obj# = c.obj# and o.owner# = u.user_id and dhlm.style = 'SNOWFLAKE' and d.invalid = 'N' and (cwm2$security.dimension_tables_visible(d.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47, -215, -216, -217))) with read only; / show errors; grant select on olapsys.ODM$olap2_join_key_column_uses to public; show errors; -- this view should be the same as all$olap2ujoin_key_column_uses except as noted create or replace view olapsys.ODM$olap2ujoin_key_column_uses as SELECT u.username owner , d.name dimension_name , nvl(h.hiername ,'NONE') hierarchy_name , l.levelname child_level_name , tu.username table_owner , t.name table_name , c.name column_name , k.keypos# position , 'FOREIGN KEY' join_key_type FROM dba_users u , 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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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 /*+ORDERED*/ u.username owner , d.name dimension_name , nvl(h.hiername ,'NONE') hierarchy_name , l.levelname child_level_name , tu.username table_owner , t.name table_name , c.name column_name , k.keypos# position , 'KEY' join_key_type FROM dba_users u , 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 u.user_id = d.owner# AND d.type# = 43 /* DIMENSION */ AND ( cwm$util.dimension_tables_visible(d.obj#) = 'Y' OR EXISTS /* SELECT ANY TABLE, CREATE, ALTER, DROP ANY DIMENSION */ (SELECT null FROM v$enabledprivs WHERE priv_number IN (-47,-215,-216,-217))) 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, column_name, position, join_key_type from olapsys.ODM$olap2_join_key_column_uses -- SEE ABOVE with read only; / show errors; grant select on olapsys.ODM$olap2ujoin_key_column_uses to public; show errors; -- this view should be the same as all$olap2_fact_level_uses except as noted create or replace view olapsys.ODM$olap2_fact_level_uses as select c.owner owner, c.name cube_name, d.owner dimension_owner, d.name dimension_name, null dimension_alias, /* dimension alias */ --(case when h.hidden = 'N' -- then h.name else null end) hierarchy_name, nvl(h.name ,'NONE') hierarchy_name, -- ALWAYS RETURN HIERARCHY NAME fdhm.irid dim_hier_combo_id, l.name level_name, u.username fact_table_owner, o.name fact_table_name, col.name column_name, decode(col.type#, 1, decode(col.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'), 2, decode(col.scale, null, decode(col.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'), 8, 'LONG', 9, decode(col.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'), 12, 'DATE', 23, 'RAW', 24, 'LONG RAW', 69, 'ROWID', 96, decode(col.charsetform, 2, 'NCHAR', 'CHAR'), 105, 'MLSLABEL', 106, 'MLSLABEL', 112, decode(col.charsetform, 2, 'NCLOB', 'CLOB'), 113, 'BLOB', 114, 'BFILE', 115, 'CFILE', 178, 'TIME(' ||col.spare1|| ')', 179, 'TIME(' ||col.spare1|| ')' || ' WITH TIME ZONE', 180, 'TIMESTAMP(' ||col.spare1|| ')', 181, 'TIMESTAMP(' ||col.spare1|| ')' || ' WITH TIME ZONE', 182, 'INTERVAL YEAR(' ||col.spare2||') TO MONTH', 183, 'INTERVAL DAY(' ||col.spare2||') TO SECOND(' || col.spare1 || ')', 208, 'UROWID', 'UNDEFINED') data_type , decode(col.length, null, 0, col.length) data_length , decode(col.precision#, null, 0, col.precision#) data_precision, fkdhlm.position, fkdhm.dimensionkeymaptype dimension_keymap_type, null foreign_key_name from olapsys.CwM2$Cube c, olapsys.CwM2$Dimension d, olapsys.CwM2$Level l, olapsys.CwM2$CubeDimensionUse cdu, olapsys.CwM2$FactDimHierMap fdhm, olapsys.CwM2$FactDimHierTplsDtl fdhtd, olapsys.CwM2$FactKeyDimHierMap fkdhm, olapsys.CwM2$FactKeyDimHierLvlMap fkdhlm, olapsys.CwM2$Hierarchy h, sys.obj$ o, sys.col$ col, dba_users u where c.irid = cdu.cube_irid and d.irid = cdu.dimension_irid and l.dimension_irid = d.irid and h.dimension_irid = d.irid and fdhm.cube_irid = c.irid and fkdhm.factDimHier_IRID = fdhm.irid and fdhm.irid = fdhtd.factdimhier_irid and fkdhlm.factkeyDimHier_IRID = fkdhm.irid and fdhm.FactTableName_ID = o.obj# and o.owner# = u.user_id and fkdhlm.columnName_ID = col.col# and o.obj# = col.obj# and fkdhlm.level_irid = l.irid and fkdhlm.hierarchy_irid = fdhtd.hier_irid and fdhtd.hier_irid = h.irid and (c.invalid = 'N' or c.invalid = 'O') and (cwm2$security.fact_table_visible(c.irid) = 'Y' OR EXISTS (select null from v$enabledprivs where priv_number in (-47))) with read only / show errors; -- this view should be the same as all$olap2ufact_level_uses except as noted create or replace view olapsys.ODM$olap2ufact_level_uses as select a.owner owner, a.cube_name cube_name, a.dimension_owner dimension_owner, a.dimension_name dimension_name, a.dimension_alias dimension_alias, nvl(b.hierarchy_name ,'NONE') hierarchy_name, 0 dim_hier_combo_id, a.level_name level_name, a.fact_table_owner fact_table_owner, a.fact_table_name fact_table_name, fk.column_name column_name, fk.position position, 'LL' dimension_keymap_type, a.foreign_key_name from olapsys.all$olap1_fact_level_uses a, all_olap_dim_hierarchies b, (select u.username table_owner, t.name table_name, c.name key_name, col.name column_name, ccol.pos# position from dba_users u, sys.obj$ t, sys.con$ c, sys.cdef$ cd, sys.col$ col, sys.ccol$ ccol where u.user_id = c.owner# and c.con# = cd.con# and cd.con# = ccol.con# and cd.obj# = t.obj# and ccol.intcol# = col.intcol# and col.obj# = t.obj# and cd.type# in (2,3,4) and t.type# in (2,4)) fk where a.dimension_name = b.dimension_name (+) and a.dimension_owner = b.owner (+) and a.fact_table_owner = fk.table_owner and a.fact_table_name = fk.table_name and a.foreign_key_name = fk.key_name union all select owner, cube_name, dimension_owner, dimension_name, dimension_alias, hierarchy_name, dim_hier_combo_id, level_name, fact_table_owner, fact_table_name, column_name, position, dimension_keymap_type, foreign_key_name from olapsys.ODM$olap2_fact_level_uses -- SEE ABOVE with read only / show errors; grant select on olapsys.ODM$olap2ufact_level_uses to public; show errors; DROP sequence OLAPSYS.dbms_mvseq; show errors; CREATE SEQUENCE OLAPSYS.dbms_mvseq START WITH 1000 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 9999 CYCLE / show errors; GRANT SELECT ON OLAPSYS.dbms_mvseq TO PUBLIC; show errors; GRANT EXECUTE ON OLAPSYS.DBMS_ODM TO PUBLIC; show errors; GRANT SELECT ON OLAPSYS.dbms_mvseq TO PUBLIC; CREATE OR REPLACE PUBLIC SYNONYM DBMS_ODM FOR OLAPSYS.DBMS_ODM; show errors;