When an mview was last successfully refreshed
-----------------------------------------------
=================================================
set lines 132
column last_refresh_type format a18
column owner format a7
column mview_name format a52
select owner, mview_name, last_refresh_type, last_refresh_date from dba_mviews;
Status of a Refresh Group Using the Job Queue
---------------------------------------------
==========================================================================
column broken format a6
alter session set nls_date_format='DD-MON-YY hh24:MI:SS';
select job, last_date last_refresh,
next_date next_refresh, total_time,
broken, failures, what
from dba_jobs
where what like '%dbms_refresh%';
Refreshes Currently Running
--------------------------------
===================================================================
The following query shows all refresh jobs that are currently running, when they started, and if any have failed.
select r.job, r.this_date, r.failures
from all_jobs_running r, all_jobs j
where j.job = r.job
and j.what like '%dbms_refresh%';
INFORMATION ON MATERIALIZED VIEW PRESENT IN THE SYSTEM
=======================================================
SELECT name, table_name, updatable, refresh_method, refresh_mode FROM user_snapshots;
NAME TABLE_NAME UPD REFRESH_MET REFRESH_
------------------------------ ------------------------------ --- ----------- --------
AMS_ACT_METRICS_MONTHLY_MV AMS_ACT_METRICS_MONTHLY_MV NO COMPLEX DEMAND
AMS_ACT_METRICS_QTRLY_MV AMS_ACT_METRICS_QTRLY_MV NO COMPLEX DEMAND
AMS_ACT_METRICS_WEEKLY_MV AMS_ACT_METRICS_WEEKLY_MV NO COMPLEX DEMAND
AMS_ACT_METRICS_YEARLY_MV AMS_ACT_METRICS_YEARLY_MV NO COMPLEX DEMAND
ASO_BI_DISC_BUK_MV ASO_BI_DISC_BUK_MV NO JOIN VIEW DEMAND
ASO_BI_QLIN_L1_MV ASO_BI_QLIN_L1_MV NO JOIN VIEW DEMAND
ASO_BI_QLIN_L2_MV ASO_BI_QLIN_L2_MV NO AGGREGATE DEMAND
set long 100000
SELECT name, query FROM user_snapshots;
NAME QUERY
------------------------------ ----------------------------------------------
AS_FORECAST_MV SELECT /*+ use_HASH(SGD) use_hash(SCD) use_hash(AFS) use_hash(ASP) use_hash(R)
*/ count(1) cnt, SGD.PARENT_GROUP_ID PARENT_SALES_GROUP_ID, R.PERIOD_NAME CONVER
SION_PERIOD_NAME, SCD.CREDIT_TYPE_ID, R.PERIOD_SET_NAME CONVERSION_PERIOD_SET_NA
ME, AFS.FORECAST_CATEGORY_ID, SUM(R.CONVERSION_STATUS_FLAG) CONVERSION_STATUS_FL
AG, SUM(SCD.SALES_CREDIT_AMOUNT*R.CONVERSION_RATE) TOTAL_AMOUNT, SUM(DECODE(nvl(
WIN_LOSS_INDICATOR,'L'),'W',SCD.SALES_CREDIT_AMOUNT* R.CONVERSION_RATE, SCD.WEIG
HTED_AMOUNT* R.CONVERSION_RATE)) WEIGHTED_AMOUNT, SUM(SCD.WON_AMOUNT* R.CONVERSI
ON_RATE) WON_AMOUNT FROM AS_PERIOD_DAYS ASP, JTF_RS_GROUPS_DENORM SGD, AS_SALES_
CREDITS_DENORM SCD, (SELECT FST.forecast_category_id, FST.START_DATE_ACTIVE,
NAME QUERY
SELECT name, last_refresh FROM user_mview_refresh_times;
NAME LAST_REFRESH
------------------------------ ---------------
BIM_I_CPB_REGN_MV 16-JUL-05
BIM_MKT_LD_CCAT_MV 16-JUL-05
BIM_MKT_LD_SRC_MV 16-JUL-05
BIM_MKT_REGN_MV 16-JUL-05
ENI_DBI_CO_OBJIDS_MV 10-JUN-08
HR_MBS_WRKFC_JCX_MV 10-JUN-08
XXAP_GL_BALANCES_MV 01-NOV-11
XXAP_LIABILITY_BALANCES_MV 01-NOV-11
select mview_name,staleness from user_mviews where mview_name='XXAP_LIABILITY_BALANCES_MV';
MVIEW_NAME STALENESS
------------------------------ -------------------
XXAP_LIABILITY_BALANCES_MV FRESH
select mview_name,staleness from user_mviews where mview_name='HR_MBS_WRKFC_JCX_MV';
MVIEW_NAME STALENESS
------------------------------ -------------------
HR_MBS_WRKFC_JCX_MV UNUSABLE