Structure - Materialized view

Create a view log for the master table

This is required for fast refresh

create materialized view log on <table>
/

or...

create materialized view log on <table>
tablespace <tablespace_name>
/

List all materialized view logs

select  log_owner,log_table from dba_mview_logs
/

Create a simple materialized view

create materialized view andy_mview
refresh [fast | complete | force]
start with sysdate
next sysdate + 1/24
with primary key
as select * from test_table
/

Fast = update changes only
Complete = wipe and repopulate the mview
Force = fast if possible, complete if not. 

Show all materialized and resfresh times

set lines 100 pages 999
col last_refresh format a20
select owner
, mview_name
, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
from dba_mviews
order by owner, last_refresh
/

Output:
OWNER           MVIEW_NAME                     LAST_REFRESH
--------------- ------------------------------ --------------------
APPS            LOCATION_PRODUCT_SVC           02/11/10 10:17
APPS            MGIARPD_PTY_CORP_HIER_DOWN_MV  05/10/10 03:11
APPS            MGIARPD_PTY_ASSC_HIER_DOWN_MV  05/10/10 03:20
APPS            MGIAR_COUNTRY_MASTER           14/07/10 10:45
APPS            MGIHZ_DMA                      14/07/10 10:46
APPS            MGIAR_IDENTIFICATION_CAT       14/07/10 10:47
APPS            MGIHZ_POSTAL_CODE              14/07/10 10:48

Show materialized view tables and masters

set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
, master_owner || '.' || master master
from dba_mview_refresh_times
/

Output:
MVIEW                                    MASTER
---------------------------------------- ----------------------------------------
MSC.MSC_SUPPLIER_TREE_MV                 MSC.MSC_TRADING_PARTNERS
MSC.MSC_ATP_PLAN_SN                      MSC.MSC_PLANS
MSC.MSC_ATP_PLAN_SN                      MSC.MSC_APPS_INSTANCES
MSC.MSC_ATP_PLAN_SN                      MSC.MSC_TRADING_PARTNERS
MSC.MSC_ATP_PLAN_SN                      MSC.MSC_SYSTEM_ITEMS

Show refresh jobs in dba_jobs

This is useful for spotting failures

set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
, log_user
, to_char(last_date, 'dd/mm/yy hh24:mi') last
, to_char(next_date, 'dd/mm/yy hh24:mi') next
, failures fail
, replace(what, '"') what
from dba_jobs
where what like '%dbms_refresh.refresh%'
/