Home » SQL & PL/SQL » SQL & PL/SQL » Materialized Views Cannot set with refresh Fast "Error" (Oracle 19)
Materialized Views Cannot set with refresh Fast "Error" [message #678503] Mon, 09 December 2019 10:55 Go to next message
greatwhite
Messages: 3
Registered: December 2019
Junior Member
I am / was attempting to modified a Materialized View. I dropped the view and recreated it after I altered the SQL. However I could not recreate it with Refresh Fast so I set it to Force for the interim.
Now I am trying to change it to Fast and I keep getting the error ORA-23413 (I have attached a snap shot of the complete message.

I don't understand why I am getting this message as the Log still exists.




/forum/index.ph/forum/fa/14234/0/
  • Attachment: Capture.JPG
    (Size: 31.78KB, Downloaded 369 times)
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678505 is a reply to message #678503] Mon, 09 December 2019 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I don't understand why I am getting this message as the Log still exists.
Either you are wrong either there is an Oracle bug, in both case we can't do anything.
Double-check you actually have a materialized log and on the correct table.

Re: Materialized Views Cannot set with refresh Fast "Error" [message #678506 is a reply to message #678505] Mon, 09 December 2019 12:40 Go to previous messageGo to next message
greatwhite
Messages: 3
Registered: December 2019
Junior Member
OK so I created a new Log/

Create Materialized View Log on "EE"."MV_DP_EE_table" WITH ROWID

Than I executed a refresh on the Materialized view and I can see data in the log.

Than I tried to change the refresh method and I get the exact same error.
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678507 is a reply to message #678506] Mon, 09 December 2019 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you say so.
As you don't show us what you do and get, that is copy and paste your SQL*Plus (before, Please read How to use [code] tags and make your code easier to read), we can't tell anything about what is or is not wrong.

Re: Materialized Views Cannot set with refresh Fast "Error" [message #678508 is a reply to message #678506] Mon, 09 December 2019 15:33 Go to previous messageGo to next message
John Watson
Messages: 8315
Registered: January 2010
Location: Global Village
Senior Member
What is the statement used to create the MV?
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678509 is a reply to message #678503] Mon, 09 December 2019 15:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2976
Registered: January 2010
Location: Connecticut, USA
Senior Member
So you decided no drop complete refresh MV and create fast refresh MV with same underlying query and got an error. Did you even read error message???? Which part of "doesn't have materialized view log" you don't understand? Did you even read on what fast refresh is and how it works?

SY.
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678510 is a reply to message #678509] Mon, 09 December 2019 16:42 Go to previous messageGo to next message
greatwhite
Messages: 3
Registered: December 2019
Junior Member
Wow first time I have ever experienced such a rude comment on a forum asking a simple question. If I knew everything about Materialized Views I probably would not have asked.

Luckily I figured out what I was doing wrong. But definitely no help from this site. Feel free to close my account.

Wow just wow
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678511 is a reply to message #678510] Mon, 09 December 2019 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
SNOWFLAKE alert!

With free advice, sometimes you get what you paid for it.
Re: Materialized Views Cannot set with refresh Fast "Error" [message #678513 is a reply to message #678510] Tue, 10 December 2019 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Luckily I figured out what I was doing wrong
So what was wrong?

Re: Materialized Views Cannot set with refresh Fast "Error" [message #678527 is a reply to message #678513] Wed, 11 December 2019 05:58 Go to previous message
Bill B
Messages: 1968
Registered: December 2004
Senior Member
did you have the rowid in the MVIEW and was it indexed?
Previous Topic: How to Pass number value to a varchar field
Next Topic: Using query to Fetch the records along with user defined column value (4 merged)
Goto Forum:
  


Current Time: Thu Aug 06 16:32:00 CDT 2020