ORA-942, ORA-1031 priv errors trying to refresh MV

From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
To: "ORACLE-L (E-mail)" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 7 Sep 2004 15:30:06 -0500
Hey all,
Messing with a fast refresh union all MV spanning two schemas.  Something like 
this:

 CREATE MATERIALIZED VIEW mv_schema.PART_USAGE_MV
 TABLESPACE QT_SMALL
 REFRESH FAST ON DEMAND
 AS
(SELECT
                '1' "MV_MARKER",
                ph.partno,
                SUM(CASE WHEN SUBSTR(time_stamp,1,4) = df.current_year THEN 
transqty ELSE NULL END) "YTD",
                COUNT(CASE WHEN SUBSTR(time_stamp,1,4) = df.current_year THEN 
transqty ELSE NULL END) "YTD_COUNT",
                SUM(CASE WHEN SUBSTR(time_stamp,1,4) = 
TO_CHAR(ADD_MONTHS(df.current_sysdate,-12),'YYYY') THEN transqty ELSE NULL END) 
"PREV_YEAR",
                COUNT(CASE WHEN SUBSTR(time_stamp,1,4) = 
TO_CHAR(ADD_MONTHS(df.current_sysdate,-12),'YYYY') THEN transqty ELSE NULL END) 
"PREV_YEAR_COUNT",
                COUNT(*) "MV_COUNT"
        FROM main_schema.part_history ph,
                main_schema.wip wp,
                mv_schema.date_format df
        WHERE ph.accountno = wp.workorderno AND
            wp.wipordertype = 'P'
        GROUP BY
                ph.partno)
        UNION ALL
        (SELECT
                '2' "MV_MARKER",
                ph.partno,
                SUM(CASE WHEN SUBSTR(time_stamp,1,4) = df.current_year THEN 
transqty ELSE NULL END) "YTD",
                COUNT(CASE WHEN SUBSTR(time_stamp,1,4) = df.current_year THEN 
transqty ELSE NULL END) "YTD_COUNT",
                SUM(CASE WHEN SUBSTR(time_stamp,1,4) = 
TO_CHAR(ADD_MONTHS(df.current_sysdate,-12),'YYYY') THEN transqty ELSE NULL END) 
"PREV_YEAR",
                COUNT(CASE WHEN SUBSTR(time_stamp,1,4) = 
TO_CHAR(ADD_MONTHS(df.current_sysdate,-12),'YYYY') THEN transqty ELSE NULL END) 
"PREV_YEAR_COUNT",
                COUNT(*) "MV_COUNT"
        FROM main_schema.part_history ph,
                main_schema.partmaster pm,
                main_schema.wip wp,
                mv_schema.date_format df
        WHERE ph.partno = pm.partno AND
            ph.accountno = wp.workorderno AND
            pm.mrp_control = 'Y' AND
            wp.wipordertype = 'P'
        GROUP BY
                ph.partno)

Somewhat icky for a posting, but the real MV is a couple hundred lines longer!  
Anyway...

I've added MV logs to MAIN_SCHEMA.PART_HISTORY, MAIN_SCHEMA.PARTMASTER, 
MAIN_SCHEMA.WIP, and MV_SCHEMA.DATE_FORMAT.  Since the MV is created in the 
MV_SCHEMA schema, I've also GRANTed SELECT on the above tables to MV_SCHEMA.

The MV creates fine, but when I try:

BEGIN
        dbms_mview.refresh('PART_USAGE_MV','f');
END;

...from MV_SCHEMA, I get:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 2

I figured that MV_SCHEMA might need access to the MV logs in MAIN_SCHEMA, so I 
GRANTed INSERT, UPDATE, DELETE, ALTER, REFERENCES, and INDEX (trying the 
shotgun affect) on them to MV_SCHEMA.  Then, the refresh causes:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
ORA-06512: at line 2

I've also stabbed at GRANTing ALTER ANY SNAPSHOT to MV_SCHEMA, even though it 
owns the MV, and it had no effect (didn't think it would).

Anyone know what privs I might be missing?  MetaLink doesn't seem to have 
anything and Orlando's not up yet for Oracle Support...

Thanks,
Rich

Rich Jesse                        System/Database Administrator
rich.jesse@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA



--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe 
To search the archives - //www.freelists.org/archives/oracle-l/
ORA-942, ORA-1031 priv errors trying to refresh MV

Other related posts:
