Home » SQL & PL/SQL » SQL & PL/SQL » ORA-12052 fast refresh materialized view, complete works (Oracle 9i)
ORA-12052 fast refresh materialized view, complete works [message #385722] Tue, 10 February 2009 16:41 Go to next message
Monica
Messages: 37
Registered: February 2000
Member
I get error:
ORA-12052: cannot fast refresh materialized view...

The command I used to refresh the MV

Complete refresh ; which is working fine
EXECUTE DBMS_MVIEW.REFRESH('ODS_SCRAP_REASONS_MV','C')

fast refreh; which is giving the error
EXECUTE DBMS_MVIEW.REFRESH('ODS_SCRAP_REASONS_MV','f')

Code for Materialized View:-
CREATE MATERIALIZED VIEW ODS_SCRAP_REASONS_MV
TABLESPACE BIMECC_TB_LARGE
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT
T.LOT_ODS_ID,
T.PRODUCT_NAME,
T.STEP_HANDLE,
T.TIME_HERE_SINCE,
T.SCRAP_QTY,
T.BONUS_QTY,
R.REASON_NAME,
R.REASON_SEQ,
S.PRODUCT_STEP_COST
FROM ODS_SCRAP_TRANSACTION T,
ODS_TRANSACTION_REASON R,
ODS_STEP S
WHERE T.WIP_TRANSACTION_ODS_ID = R.WIP_TRANSACTION_ODS_ID AND
T.LOT_ODS_ID = R.LOT_ODS_ID AND
S.LOT_ODS_ID = T.LOT_ODS_ID AND
S.STEP_HANDLE = T.STEP_HANDLE AND
S.PRODUCT_NAME = T.PRODUCT_NAME AND
S.TIME_HERE_SINCE = T.TIME_HERE_SINCE

Code for Materialized View Logs
CREATE MATERIALIZED VIEW LOG ON ODS_SCRAP_TRANSACTION TABLESPACE BIMECC_TB_LARGE WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON ODS_STEP TABLESPACE BIMECC_TB_LARGE WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON ODS_TRANSACTION_REASON TABLESPACE BIMECC_TB_LARGE WITH ROWID ;

Report message to a moderator

Re: ORA-12052 fast refresh materialized view, complete works [message #385724 is a reply to message #385722] Tue, 10 February 2009 17:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/f/1/136107/

One of the notices on URL above is
SEARCH before posting
Search words: ORA-12052 Total hits: 13

So are you incapable or unwilling to follow Posting Guidelines?

Report message to a moderator

Re: ORA-12052 fast refresh materialized view, complete works [message #385787 is a reply to message #385722] Wed, 11 February 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
ORA-12052: cannot fast refresh materialized view %s.%s
 *Cause:  Either ROWIDs of certain tables were missing in the definition or
          the inner table of an outer join did not have UNIQUE constraints on
          join columns.
 *Action: Specify the FORCE or COMPLETE option. If this error is got
          during creation, the materialized view definition may have be
          changed. Refer to the documentation on materialized views.

Did you check this?

Regards
Michel

Report message to a moderator

Re: ORA-12052 fast refresh materialized view, complete works [message #385862 is a reply to message #385787] Wed, 11 February 2009 09:01 Go to previous messageGo to next message
Monica
Messages: 37
Registered: February 2000
Member
Yes Mike I did. But thanks.

Report message to a moderator

Re: ORA-12052 fast refresh materialized view, complete works [message #385915 is a reply to message #385862] Wed, 11 February 2009 20:09 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Monica wrote on Thu, 12 February 2009 02:01
Yes Mike I did. But thanks.

Quote:
SELECT
T.LOT_ODS_ID,
T.PRODUCT_NAME,
T.STEP_HANDLE,
T.TIME_HERE_SINCE,
T.SCRAP_QTY,
T.BONUS_QTY,
R.REASON_NAME,
R.REASON_SEQ,
S.PRODUCT_STEP_COST
FROM ODS_SCRAP_TRANSACTION T,
ODS_TRANSACTION_REASON R,
ODS_STEP S

I don't see any ROWIDs selected in the Materialized View.

Use DBMS_MVIEW.EXPLAIN_MVIEW to list reasons why you cannot fast refresh a MV.
Ross Leishman

Report message to a moderator

Re: ORA-12052 fast refresh materialized view, complete works [message #385916 is a reply to message #385722] Wed, 11 February 2009 20:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
WHERE T.WIP_TRANSACTION_ODS_ID = R.WIP_TRANSACTION_ODS_ID AND
T.LOT_ODS_ID = R.LOT_ODS_ID AND
S.LOT_ODS_ID = T.LOT_ODS_ID AND
S.STEP_HANDLE = T.STEP_HANDLE AND
S.PRODUCT_NAME = T.PRODUCT_NAME AND
S.TIME_HERE_SINCE = T.TIME_HERE_SINCE

Do indexes exist on all columns above & are statistics current?

Report message to a moderator

Re: ORA-12052 fast refresh materialized view, complete works [message #385964 is a reply to message #385916] Thu, 12 February 2009 01:35 Go to previous message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
BlackSwan wrote on Thu, 12 February 2009 03:14
WHERE T.WIP_TRANSACTION_ODS_ID = R.WIP_TRANSACTION_ODS_ID AND
T.LOT_ODS_ID = R.LOT_ODS_ID AND
S.LOT_ODS_ID = T.LOT_ODS_ID AND
S.STEP_HANDLE = T.STEP_HANDLE AND
S.PRODUCT_NAME = T.PRODUCT_NAME AND
S.TIME_HERE_SINCE = T.TIME_HERE_SINCE

Do indexes exist on all columns above & are statistics current?


I fail to see that your answer has something to do with a MV not able to fast refresh.

Report message to a moderator

Previous Topic: doubt with case
Next Topic: Maximum jobs that can be submitted
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Sep 03 02:58:00 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.