Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORACLE ERROR ORA-32314: REFRESH FAST unsupported after deletes/updates

user13168644Jan 7 2011 — edited Jan 7 2011
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> 
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 4 2011
Added on Jan 7 2011
2 comments
2,318 views