hi,
I have a materialized view created with fast refresh on demand option. Now when i try to refresh it manually the next day, it gives me an error . I have both count(*) and count(1) inside the materialized view. There is update and delete transactions done on most of the tables. The refresh is set to be at 4 am though a refresh group . But it fails with the error shown below . Please help. thanks
here is the script from toad
DROP MATERIALIZED VIEW MYPORTAL.ACCOUNT_COST_CATEGORY_MV;
CREATE MATERIALIZED VIEW MYPORTAL.ACCOUNT_COST_CATEGORY_MV
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS
/* Formatted on 2011/01/07 10:04 (Formatter Plus v4.8.8) */
SELECT ac.account_id,
ac.account_manager mgr_id,
ac.account_number,
ac.account_name,
bc.burden_center_name burden_center,
p.surname_isi || ', ' || p.first_name account_manager,
p.division_id,
item.code_id,
item.code_name cost_category_item,
TO_NUMBER (TO_CHAR (ap.first_day, 'YYYY')) period_year,
ap.first_day period,
ap.fiscal_system_period_cal_yr_eq || ', ' || ap.fiscal_year period_asr,
COUNT (*) total_row_count,
COUNT (1) total_one_count,
SUM (NVL (ad.current_expense, 0)) expense,
SUM (NVL (ad.total_budget, 0)) budget,
SUM (NVL (ad.cumltv_expense, 0)) cumltv_expense,
SUM (NVL (ad.commitments, 0)) commitments,
SUM (NVL (ad.budget_balance, 0)) budget_balance
FROM accounts ac,
burden_centers bc,
personnel p,
asr_headers ah,
asr_details ad,
codes item,
codes costcat,
all_periods ap,
codes cost_element,
accounting_base_group abg
WHERE ac.account_manager = p.personnel_id
AND ac.burden_center_id = bc.burden_center_id
AND ac.account_id = ah.account_id
AND ah.asr_header_id = ad.asr_header_id
AND ah.period_asr =
ap.fiscal_system_period_cal_yr_eq || ', ' || ap.fiscal_year
AND abg.cost_element_cat_item_id = item.code_id
AND cost_element.code_id = abg.cost_element_id
AND ad.object_code = cost_element.code_value
AND item.code_parent_id = costcat.code_id
AND ( costcat.code_name = 'Report 1'
OR (costcat.code_name = 'Base' AND item.code_name = 'MTDC')
)
GROUP BY ac.account_id,
ac.account_manager,
ac.account_number,
ac.account_name,
bc.burden_center_name,
p.surname_isi || ', ' || p.first_name,
p.division_id,
item.code_id,
item.code_name,
TO_NUMBER (TO_CHAR (ap.first_day, 'YYYY')),
ap.first_day,
ap.fiscal_system_period_cal_yr_eq || ', ' || ap.fiscal_year;
COMMENT ON MATERIALIZED VIEW MYPORTAL.ACCOUNT_COST_CATEGORY_MV IS 'snapshot table for snapshot MYPORTAL.ACCOUNT_COST_CATEGORY_MV';
CREATE INDEX MYPORTAL.ACCOUNT_COST_CAT_MV_IDX ON MYPORTAL.ACCOUNT_COST_CATEGORY_MV
(ACCOUNT_ID, CODE_ID, PERIOD)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
-- Note: Index I_SNAP$_ACCOUNT_COST_CATEG will be created automatically
-- by Oracle with the associated materialized view.
here is the error :
10:10:04 SQL> exec dbms_mview.refresh('account_cost_category_mv');
BEGIN dbms_mview.refresh('account_cost_category_mv'); END;
*
ERROR at line 1:
ORA-32314: REFRESH FAST of "MYPORTAL"."ACCOUNT_COST_CATEGORY_MV" unsupported after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at line 1
10:10:14 SQL>