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

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_at_quadtechworld.com      QuadTech, Sussex, WI USA

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/