You Asked
Hi Tom,
I wanted to create Materialized view with FAST refresh method but at the creation time it is giving error message
ORA-12018: following error encountered during code generation for "AUD"."AUD_PARTS_MVIEW".
So, I created same Mview with COMPLETE Refresh method and it got created.
After that I alter the mview and make FAST refresh method.
point no. 1 - it is not running regularly at schedule time.
point no. 2 - I am trying to run it manually with FAST refresh mode again it is giving me same error.
EXEC DBMS_MVIEW.REFRESH('AUD.AUD_PARTS_MVIEW','F');
Error :-
ORA-12018: following error encountered during code generation for "AUD"."AUD_PARTS_MVIEW"
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1
Mview code -
DROP MATERIALIZED VIEW AUD.AUD_PARTS_MVIEW;
CREATE MATERIALIZED VIEW AUD.AUD_PARTS_MVIEW
TABLESPACE AUD_SNAPDATA_M
PCTUSED 40
PCTFREE 10
INITRANS 24
MAXTRANS 255
STORAGE (
INITIAL 4M
NEXT 4M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOCACHE
NOLOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST
START WITH TO_DATE('09-03-2016 09:05:04','dd-mm-yyyy hh24:mi:ss')
NEXT SYSDATE+4/24
WITH PRIMARY KEY
AS
/* Formatted on 3/11/2016 6:39:43 PM (QP5 v5.256.13226.35510) */
SELECT PART_NUM,
PRICING_REGION_CODE,
PART_DESC,
CURR_DIST_PRICE,
CURR_DIST_PRICE_EFFECTIVE,
OLD_DIST_PRICE,
CURR_DEALER_PRICE,
CURR_DEALER_PRICE_EFFECTI,
OLD_DEALER_PRICE,
CURR_SA_PRICE,
CURR_SA_PRICE_EFFECTIVE,
OLD_SA_PRICE,
CURR_FLEET_PRICE,
CURR_FLEET_PRICE_EFFECTIVE,
OLD_FLEET_PRICE,
DELETE_STATUS,
CU_PACKAGE_QUANTITY,
CU_PACKAGE_HEIGHT,
CU_PACKAGE_LENGTH,
CU_PACKAGE_WIDTH,
CU_PACKAGE_WEIGHT,
CU_SPC_CODE,
CU_INTL_REPR_TRD_NET_PRICE,
CU_DIST_NET_INT_PRICE,
PART_DESC_LONG,
CU_STATUS_CODE,
WHLSL_COMP_CODE,
RESP_CODE,
FIN_RESP_CODE,
RECON_SPC_MAJ_CODE,
CURR_PARTNER_PRICE,
CURR_PARTNER_PRICE_EFFECTIVE,
OLD_PARTNER_PRICE
FROM rss.parts@RSS_PRODN.WORLD;
COMMENT ON MATERIALIZED VIEW AUD.AUD_PARTS_MVIEW IS 'snapshot table for snapshot AUD.AUD_PARTS_MVIEW';
CREATE OR REPLACE PUBLIC SYNONYM AUD_PARTS_MVIEW FOR AUD.AUD_PARTS_MVIEW;
CREATE UNIQUE INDEX AUD.PARTS_PK ON AUD.AUD_PARTS_MVIEW
(PART_NUM, PRICING_REGION_CODE)
LOGGING
TABLESPACE AUD_SNAPDATA_L
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128M
NEXT 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
Please help me to resolve this issue ASAP. I am not able to understand why it is giving this error and in users_mview table it is showing COMPILE_STATE as COMPILATION_ERROR.
Mview log -
CREATE MATERIALIZED VIEW LOG ON rss.parts WITH PRIMARY KEY, ROWID INCLUDiNG NEW VALUES;
and Connor said...
My guess is that it is a privileges issue on one of the nodes, because I can't get failures on my test machine here - see example at end.
So try this:
GRANT SELECT ON MLOG$_<tableName> to XXX;
where XXX is the account that will accessing the log (ie via the db link).
== DATABASE "DB11"
SQL> create table parts as
2 SELECT
3 rownum PART_NUM,
4 rownum PRICING_REGION_CODE,
5 rownum PART_DESC,
6 rownum CURR_DIST_PRICE,
7 rownum CURR_DIST_PRICE_EFFECTIVE,
8 rownum OLD_DIST_PRICE,
9 rownum CURR_DEALER_PRICE,
10 rownum CURR_DEALER_PRICE_EFFECTI,
11 rownum OLD_DEALER_PRICE,
12 rownum CURR_SA_PRICE,
13 rownum CURR_SA_PRICE_EFFECTIVE,
14 rownum OLD_SA_PRICE,
15 rownum CURR_FLEET_PRICE,
16 rownum CURR_FLEET_PRICE_EFFECTIVE,
17 rownum OLD_FLEET_PRICE,
18 rownum DELETE_STATUS,
19 rownum CU_PACKAGE_QUANTITY,
20 rownum CU_PACKAGE_HEIGHT,
21 rownum CU_PACKAGE_LENGTH,
22 rownum CU_PACKAGE_WIDTH,
23 rownum CU_PACKAGE_WEIGHT,
24 rownum CU_SPC_CODE,
25 rownum CU_INTL_REPR_TRD_NET_PRICE,
26 rownum CU_DIST_NET_INT_PRICE,
27 rownum PART_DESC_LONG,
28 rownum CU_STATUS_CODE,
29 rownum WHLSL_COMP_CODE,
30 rownum RESP_CODE,
31 rownum FIN_RESP_CODE,
32 rownum RECON_SPC_MAJ_CODE,
33 rownum CURR_PARTNER_PRICE,
34 rownum CURR_PARTNER_PRICE_EFFECTIVE,
35 rownum OLD_PARTNER_PRICE
36 from dual
37 connect by level <= 10000;
Table created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter table parts add constraint parts_pk primary key ( part_num , PRICING_REGION_CODE);
Table altered.
SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON parts WITH PRIMARY KEY, ROWID INCLUDiNG NEW VALUES;
Materialized view log created.
--
-- This DML run *after* the mview was created (see below)
--
SQL>
SQL> delete from parts where PART_NUM < 10 ;
9 rows deleted.
SQL>
SQL> update parts set OLD_PARTNER_PRICE = OLD_PARTNER_PRICE * 10
2 where PART_NUM between 1000 and 1010;
11 rows updated.
SQL>
SQL> commit;
Commit complete.
==================== DATABASE "NP12"
SQL> CREATE MATERIALIZED VIEW AUD_PARTS_MVIEW
2 BUILD IMMEDIATE
3 REFRESH FAST
4 START WITH sysdate
5 NEXT SYSDATE+60/86400
6 WITH PRIMARY KEY
7 AS
8 SELECT PART_NUM,
9 PRICING_REGION_CODE,
10 PART_DESC,
11 CURR_DIST_PRICE,
12 CURR_DIST_PRICE_EFFECTIVE,
13 OLD_DIST_PRICE,
14 CURR_DEALER_PRICE,
15 CURR_DEALER_PRICE_EFFECTI,
16 OLD_DEALER_PRICE,
17 CURR_SA_PRICE,
18 CURR_SA_PRICE_EFFECTIVE,
19 OLD_SA_PRICE,
20 CURR_FLEET_PRICE,
21 CURR_FLEET_PRICE_EFFECTIVE,
22 OLD_FLEET_PRICE,
23 DELETE_STATUS,
24 CU_PACKAGE_QUANTITY,
25 CU_PACKAGE_HEIGHT,
26 CU_PACKAGE_LENGTH,
27 CU_PACKAGE_WIDTH,
28 CU_PACKAGE_WEIGHT,
29 CU_SPC_CODE,
30 CU_INTL_REPR_TRD_NET_PRICE,
31 CU_DIST_NET_INT_PRICE,
32 PART_DESC_LONG,
33 CU_STATUS_CODE,
34 WHLSL_COMP_CODE,
35 RESP_CODE,
36 FIN_RESP_CODE,
37 RECON_SPC_MAJ_CODE,
38 CURR_PARTNER_PRICE,
39 CURR_PARTNER_PRICE_EFFECTIVE,
40 OLD_PARTNER_PRICE
41 FROM parts@db11;
Materialized view created.
SQL> select count(*) from AUD_PARTS_MVIEW where PART_NUM < 10 ;
COUNT(*)
----------
9
SQL> select OLD_PARTNER_PRICE from AUD_PARTS_MVIEW where PART_NUM between 1000 and 1010;
OLD_PARTNER_PRICE
-----------------
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
11 rows selected.
SQL> @jobs
JOB_NAME WHAT NEXT_DATE FAIL E
---------------------------------------- ------------------------------------------------------------ --------------- ---------- -
53 dbms_refresh.refresh('"MCDONAC"."AUD_PARTS_MVIEW"'); 15/03 14:38 0 Y
(waited for a couple of minutes)
SQL> select count(*) from AUD_PARTS_MVIEW where PART_NUM < 10 ;
COUNT(*)
----------
0
SQL> select OLD_PARTNER_PRICE from AUD_PARTS_MVIEW where PART_NUM between 1000 and 1010;
OLD_PARTNER_PRICE
-----------------
10000
10010
10020
10030
10040
10050
10060
10070
10080
10090
10100
11 rows selected.
SQL>
SQL>
(and explicit fast refresh worked as well)
SQL> exec DBMS_MVIEW.REFRESH('"MCDONAC"."AUD_PARTS_MVIEW"','F');
PL/SQL procedure successfully completed.