ORA-12034:materialized view log on "snapshot_name"younger than last refresh
536804Oct 12 2006 — edited Oct 17 2006Hello,
We want to upgrade our database from version 8.1.7 to version10.0.2 and the hardware platform is different.So we use export/import.But when we import the source database data into the new , some of the materialized view cann't run normally.When the materialized view freshes ,it always raise the ORA-12034 error.
I do a complete refresh , then run a fast refresh, it also raise ORA-12034.I drop the MV ,then rebuild it and do a complete refresh,then run fast refresh .it also raise the same error. I don't know what l should do next?How I could run my MV fast refresh without error ? Thanks a lot! The following are the examples,please do me a favor.
--run a fast fresh ,a error raise
SQL> exec dbms_refresh.refresh('"MBARC"."MTL_SYSTEM_ITEMS_TL"');
begin dbms_refresh.refresh('"MBARC"."MTL_SYSTEM_ITEMS_TL"'); end;
ORA-12034: materialized view log on "INV"."MTL_SYSTEM_ITEMS_TL" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
--do a complete fresh
SQL> exec dbms_snapshot.refresh('MTL_SYSTEM_ITEMS_TL','C');
PL/SQL procedure successfully completed
--then do a fast fresh,the error alse raise
SQL> exec dbms_refresh.refresh('"MBARC"."MTL_SYSTEM_ITEMS_TL"');
begin dbms_refresh.refresh('"MBARC"."MTL_SYSTEM_ITEMS_TL"'); end;
ORA-12034: materialized view log on "INV"."MTL_SYSTEM_ITEMS_TL" younger than last refresh
--drop the MV
SQL> DROP MATERIALIZED VIEW MBARC.MTL_SYSTEM_ITEMS_TL;
Materialized view dropped
--rebuild it
CREATE MATERIALIZED VIEW MBARC.MTL_SYSTEM_ITEMS_TL
TABLESPACE BARC
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE BARC
REFRESH FAST
START WITH sysdate
NEXT SYSDATE + 20/(24*60)
WITH ROWID
AS
SELECT "MTL_SYSTEM_ITEMS_TL"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID"
FROM "INV"."MTL_SYSTEM_ITEMS_TL"@MRP.WORLD "MTL_SYSTEM_ITEMS_TL";
--run a complete refresh
SQL> exec dbms_snapshot.refresh('MTL_SYSTEM_ITEMS_TL','C');
PL/SQL procedure successfully completed
--run the fast refresh,also the same error
SQL> exec dbms_refresh.refresh('"MBARC"."MTL_SYSTEM_ITEMS_TL"');
begin dbms_refresh.refresh('"MBARC"."MTL_SYSTEM_ITEMS_TL"'); end;
ORA-12034: materialized view log on "INV"."MTL_SYSTEM_ITEMS_TL" younger than last refresh