You Asked
We have Materialized Views which reference tables in other schemas.
We can refresh/compile the Materialized Views from the command line however
when we refresh/compile the Materialized View from within a procedure
the job immediately aborts with an error that a table does not exist and
the materialized view is left with a "compile_error" state.
A potential solution seems to be to create a local View pointing to the
remote table (in another schema) and reference the local View within the
Materialized View.
We have tested that this works in the below simple examples but wonder:
1) Why Materialized Views won't refresh from within a procedure?
2) If referencing a remote table View within a Materialized View is an
acceptable practice?
===
--- Create Sample Tables
--- Find and Replace all instances of SCHEMA1 and SCHEMA2 for valid schema names
CREATE TABLE SCHEMA1.TAB1
("COL1" int, "COL2" int, "COL3" int)
;
INSERT ALL
INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
VALUES (1, 1, 1)
INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
VALUES (2, 2, 2)
SELECT * FROM dual
;
CREATE TABLE SCHEMA1.TAB3
("COL1" int, "COL2" int, "COL3" int)
;
INSERT ALL
INTO SCHEMA1.TAB3 ("COL1", "COL2", "COL3")
VALUES (1, 1, 1)
INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
VALUES (2, 2, 2)
SELECT * FROM dual
;
CREATE TABLE SCHEMA2.TAB2
("COL1" int, "COL2" int, "COL3" int)
;
INSERT ALL
INTO SCHEMA2.TAB2 ("COL1", "COL2", "COL3")
VALUES (1, 1, 1)
INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
VALUES (2, 2, 2)
SELECT * FROM dual
;
-- Create Sample Materialized View
DROP MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST;
CREATE MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST
AS
SELECT
T1.COL1,
T1.COL2,
T2.COL3
FROM
SCHEMA2.TAB2 T1
JOIN SCHEMA1.TAB1 T2
ON T1.COL2 = T2.COL2;
-- View Materialized View
SELECT * FROM SCHEMA1.MV_REFRESH_TEST
-- Create Procedure to Compile and Refresh Materialized View
CREATE OR REPLACE PROCEDURE SCHEMA1.PR_REFRESH_COMPILE
IS
BEGIN
EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
dbms_mview.Refresh (list => 'SCHEMA1.MV_REFRESH_TEST', method => 'C');
END;
-- Call Procedure, Produces errors
BEGIN PR_REFRESH_COMPILE; END;
-- View state of Materialized View
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
-- Call Compile and Refresh in an anonymous block, completes without errors
BEGIN
EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
dbms_mview.Refresh (list => 'SCHEMA1.MV_REFRESH_TEST', method => 'C');
END;
-- View state of Materialized View, Still reports compilation errors
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
-- Compile Materialized View
ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile
-- View state of Materialized View, Still reports compilation errors
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
-- Alternate Materialized View using only tables within the SCHEMA1 schema works fine
DROP MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST;
CREATE MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST
AS
SELECT
T1.COL1,
T1.COL2,
T2.COL3
FROM
SCHEMA1.TAB3 T1
JOIN SCHEMA1.TAB1 T2
ON T1.COL2 = T2.COL2;
-- Call Procedure
BEGIN PR_REFRESH_COMPILE; END;
-- View state of Materialized View, Successful
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
-- Call Compile and Refresh in an anonymous block
BEGIN
EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
dbms_mview.Refresh (list => 'SCHEMA1.MV_REFRESH_TEST', method => 'C');
EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
END;
-- View state of Materialized View, Successful
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
-- Compile Materialized View
ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile
-- View state of Materialized View, Successful
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
=== Solution
--- Create View of remote table
--- Note: that we had problems with GRANT permissions to create the view in SCHEMA1
CREATE OR REPLACE VIEW SCHEMA1.MV_REFRESH_T1
AS
SELECT COL1, COL2
FROM SCHEMA2.TAB2 T1;
--- Create Materialized View referencing View to remote table
DROP MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST;
CREATE MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST
AS
SELECT T1.COL1, T1.COL2, T2.COL3
FROM SCHEMA1.MV_REFRESH_T1 T1, TAB1 T2
WHERE COL2 = COL2;
--- Create Procedure to Compile and Refresh Materialized View
CREATE OR REPLACE PROCEDURE SCHEMA1.PR_REFRESH_COMPILE
IS
BEGIN
EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
dbms_mview.Refresh (list => 'SCHEMA1.MV_REFRESH_TEST', method => 'C');
END;
--- Check state of Materialized View
SELECT LAST_REFRESH_DATE, MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
--==LAST_REFRESH_DATE MVIEW_NAME STALENESS LAST_REFRESH_TYPE COMPILE_STATE
--==8/31/2016 4:34:47 PM MV_REFRESH_TEST FRESH COMPLETE VALID
--- Run Compile and Refresh Procedure
BEGIN PR_REFRESH_COMPILE; END;
--- Check state of Materialized View
SELECT LAST_REFRESH_DATE, MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
--==LAST_REFRESH_DATE MVIEW_NAME STALENESS LAST_REFRESH_TYPE COMPILE_STATE
--==8/31/2016 4:39:41 PM MV_REFRESH_TEST FRESH COMPLETE VALID
and Connor said...
Sorry - I'm not sure I follow. When I run the first part of your script it does what I expect
SQL> grant resource, connect, create materialized view , create table to schema1 identified by schema1;
Grant succeeded.
SQL> grant resource, connect, create materialized view , create table to schema2 identified by schema2;
Grant succeeded.
SQL>
SQL> alter user schema1 quota 100m on users;
User altered.
SQL> alter user schema2 quota 100m on users;
User altered.
SQL>
SQL> drop table schema1.tab1 purge;
drop table schema1.tab1 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table schema2.tab2 purge;
drop table schema2.tab2 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table schema1.tab3 purge;
drop table schema1.tab3 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> CREATE TABLE SCHEMA1.TAB1
2 ("COL1" int, "COL2" int, "COL3" int)
3 ;
Table created.
SQL>
SQL>
SQL> INSERT ALL
2 INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
3 VALUES (1, 1, 1)
4 INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
5 VALUES (2, 2, 2)
6 SELECT * FROM dual
7 ;
2 rows created.
SQL>
SQL>
SQL> CREATE TABLE SCHEMA1.TAB3
2 ("COL1" int, "COL2" int, "COL3" int)
3 ;
Table created.
SQL>
SQL>
SQL>
SQL> INSERT ALL
2 INTO SCHEMA1.TAB3 ("COL1", "COL2", "COL3")
3 VALUES (1, 1, 1)
4 INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
5 VALUES (2, 2, 2)
6 SELECT * FROM dual
7 ;
2 rows created.
SQL>
SQL>
SQL>
SQL> CREATE TABLE SCHEMA2.TAB2
2 ("COL1" int, "COL2" int, "COL3" int)
3 ;
Table created.
SQL>
SQL>
SQL>
SQL> INSERT ALL
2 INTO SCHEMA2.TAB2 ("COL1", "COL2", "COL3")
3 VALUES (1, 1, 1)
4 INTO SCHEMA1.TAB1 ("COL1", "COL2", "COL3")
5 VALUES (2, 2, 2)
6 SELECT * FROM dual
7 ;
2 rows created.
SQL>
SQL> DROP MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST;
DROP MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST
*
ERROR at line 1:
ORA-12003: materialized view or zonemap "SCHEMA1"."MV_REFRESH_TEST" does not exist
SQL>
SQL> --
SQL> -- my addition
SQL> --
SQL> grant select on SCHEMA2.TAB2 to schema1;
Grant succeeded.
SQL>
SQL> CREATE MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST
2 AS
3 SELECT
4 T1.COL1,
5 T1.COL2,
6 T2.COL3
7 FROM
8 SCHEMA2.TAB2 T1
9 JOIN SCHEMA1.TAB1 T2
10 ON T1.COL2 = T2.COL2;
Materialized view created.
SQL>
SQL> SELECT * FROM SCHEMA1.MV_REFRESH_TEST;
COL1 COL2 COL3
---------- ---------- ----------
1 1 1
SQL>
SQL> CREATE OR REPLACE PROCEDURE SCHEMA1.PR_REFRESH_COMPILE
2 IS
3 BEGIN
4 EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
5 dbms_mview.Refresh (list => 'SCHEMA1.MV_REFRESH_TEST', method => 'C');
6 END;
7 /
Procedure created.
SQL>
SQL> conn schema1/schema1
Connected.
SQL> exec PR_REFRESH_COMPILE;
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
MVIEW_NAME
--------------------------------------------------------------------------------------------------------------------------------
STALENESS LAST_REF COMPILE_STATE
------------------- -------- -------------------
MV_REFRESH_TEST
FRESH COMPLETE VALID
SQL>
SQL> BEGIN
2 EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW SCHEMA1.MV_REFRESH_TEST compile';
3 dbms_mview.Refresh (list => 'SCHEMA1.MV_REFRESH_TEST', method => 'C');
4 END;
5 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME LIKE 'MV_%' ORDER BY MVIEW_NAME ;
MVIEW_NAME
--------------------------------------------------------------------------------------------------------------------------------
STALENESS LAST_REF COMPILE_STATE
------------------- -------- -------------------
MV_REFRESH_TEST
FRESH COMPLETE VALID
SQL>