Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-12034:materialized view log on "snapshot_name"younger than last refresh

536804Oct 12 2006 — edited Oct 17 2006
Hello,
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 14 2006
Added on Oct 12 2006
4 comments
2,090 views