This script will allow you to determine the existence of Materialized Views generated via the OLAP option.


set lines 110 pages 500
col cube_mv for a35 hea 'Cube Materialized View'
col rewrite_enabled for a7 hea 'ReWrite'
col build_mode for a8 hea 'Build|Mode'
col refresh_mode for a8 hea 'Refresh|Mode'
col refresh_method hea 'Refresh|Method'
col last_refresh_date hea 'Refresh|Last Date' col staleness hea 'Staleness'

select owner||'.'||mview_name cube_mv,rewrite_enabled
, refresh_mode,refresh_method,last_refresh_date,staleness
from all_mviews
where container_name like 'CB$%'
order by cube_mv;

................................................................................Build... Refresh..Refresh..Refresh
Cube Materialized View....................ReWrite Mode.....Mode.... Method...Last Date Staleness
-------------------------------------------------------------- ------- -------- --------.-------- --------- ----------------------------
DM.CB$CHANNEL_SALES_CHANNEL........ N...... DEFERRED.DEMAND...COMPLETE 29-OCT-07 FRESH..............
DM.CB$CUSTOMER_REGIONAL................ N.......DEFERRED.DEMAND...COMPLETE 29-OCT-07 FRESH...............
DM.CB$PRODUCT_STANDARD....................N.......DEFERRED.DEMAND...COMPLETE 29-OCT-07 FRESH...............
DM.CB$SALES_CUBE................................Y.......DEFERRED.DEMAND...FORCE... 29-OCT-07 FRESH...............
DM.CB$TIME_CALENDAR..........................N.......DEFERRED.DEMAND...COMPLETE 29-OCT-07 FRESH...............
DM.CB$TIME_FISCAL..............................N.......DEFERRED.DEMAND...COMPLETE 29-OCT-07 FRESH...............