oracle aide

September 24, 2007

DBMS_MVIEW.REFRESH fails with ORA-01031

Filed under: mview, replication — Tags: , , — oracleaide @ 8:15 pm

Problem statement:

  • user_a owns a materialized view (mv_demo);
  • user_b wants to refresh the mv_demo mview in the user_a schema;
  • dbms_refresh fails with ORA-01031: insufficient privileges.

  • Example:

    SQL> exec dbms_mview.refresh('user_a.mv_demo');
    BEGIN dbms_mview.refresh('user_a.mv_demo'); END;
    
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges
    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
    

    Solution: grant ALTER ANY MATERIALIZED VIEW to user_b.

    Advertisements

    1 Comment »

    1. Above solution won’t work for me. i am runing 10203 oracle verion.
      MView DDl
      CREATE MATERIALIZED VIEW “test_mv”
      ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE “SGTAB”
      BUILD IMMEDIATE
      USING INDEX
      REFRESH COMPLETE ON DEMAND
      USING DEFAULT LOCAL ROLLBACK SEGMENT
      DISABLE QUERY REWRITE
      AS SELECT invoice_id invoice_no,
      invoice_line_no
      FROM history@edw_lnk
      WHERE TRUNC(invoice_date) >= (SELECT NVL(MIN(invoice_date), SYSDATE-30)
      FROM own.ship_fact
      WHERE account_month_no = (SELECT SUBSTR(partition_name,
      LENGTH(partition_name) – 6 + 1)
      FROM dba_tab_partitions
      WHERE table_name = ‘SHIP_FACT’
      AND partition_position = 1))
      AND TRUNC(invoice_date) <= NVL((SELECT load_to_date
      FROM own.v_actv_source_lkp), SYSDATE)
      AND invoice_qty 0
      AND record_type ‘DM’;

      Comment by Aalok — April 1, 2008 @ 6:59 am


    RSS feed for comments on this post. TrackBack URI

    Leave a Reply

    Please log in using one of these methods to post your comment:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Blog at WordPress.com.

    %d bloggers like this: