Creating materialized view based on OEBS editionables

[ANIETO]

Hello Erman, I hope you are fine.

We are upgrading EBS from 12.1.3 to 12.2.10.
We have many external systems connected via materialized views (external System) and MVLogs in our system (EBS).

When trying to create materialized view in other servers (other Databases) now pointing APPS and not the schema owner, we get the error:

ORA-23413: table "APPLSYS"."FND_FLEX_VALUES_TL#" does not have a materialized view log

We find notes for Materialized view logs and MV within OEBS, but not for MV in other environments.

What is the procedure for using OEBS tables, create materialized view logs, In These cases?

We appreciate any support.

[ErmanArslansOracleBlog]

APPLSYS.FND_FLEX_VALUES_TL# is an editioning view.
I think you are trying to create an MV on Standard EBS tables..

On an editioned database, you can no longer create a materialized view directly.

Please follow MOS note -> Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1) : Section 1.4.3.5: Materialized Views

[ErmanArslansOracleBlog]

+ Section 2.3.6: Materialized View (MV)...

[ANIETO]

Thanks Erman, but in this case, our issue is on what object do we create the MV MVLogs? to replicate the changes.. I can't do it about APPLSYS.FND_FLEX_VALUES_TL, neither about APPLSYS.FND_FLEX_VALUES_TL# and APPS.FND_FLEX_VALUES_TL is a synonym.

The note 1577661.1 talks about how to do it within OEBS, in our case the MVLogs are in our OEBS DB and the MVs are in another DB.

Thanks

[ErmanArslansOracleBlog]

AS for MV logs; We have a restriction there for EBS 12.2..

On an editioned database 12.2 environment, a materialized view (MV) definition currently may not reference editioned objects. This is known limitation of 12.2 environment

"That" restriction -> Is It Supported To Create Materialised View Logs On Oracle HR Base Tables? (Doc ID 2322661.1)

However; I tested it and I was able to do a fast refresh on a MV based on FND_USER(as the master table)

But! this is not documented. So you need ask Oracle Support and get their approval for doing such a thing.

Here is what I did.. (note that those user_id is just an example for the PK..  It is not an actual PK of the base table...)

CREATE or REPLACE VIEW ERBANT3#
AS
SELECT
* from fnd_user

begin
ad_zd_mview.upgrade('APPS','ERBANT3');
end;
/

CREATE MATERIALIZED VIEW LOG ON applsys.fnd_user WITH PRIMARY KEY (user_name);

exec DBMS_MVIEW.refresh('ERBANT3','c');
exec DBMS_MVIEW.refresh('ERBANT3','f'); -- fast refresh successful..

actually, it is seen in the DBA_SNAPSHOTS table.. You can check there and understand if you can make fast refresh for a MV... You should see YES in CAN_USE_LOG columns and you should see PRIMARY_KEY for the REFRESH_METHOD column..

Otherwise you get errors like ORA-12004..

But! again, please ask this to Oracle Support.

[ANIETO]

Thanks Erman. I replicated the changes and it works for me, but if I do the same for the FND_FLEX_VALUES_TL table, when creating the MV it sends me the error "ORA-12026: invalid filter column detected".

CREATE MATERIALIZED VIEW LOG ON applsys.FND_FLEX_VALUES_TL WITH PRIMARY KEY (flex_value_id, language, zd_edition_name);

Are the edition fields generating the error?
We appreciate your support.

[ErmanArslansOracleBlog]

This table has no primary key. So normally you need to get ORA-12014.. ORA-12016 is weird and may be investigated with a low level sql trace.. Probably because of EBR..  But no need to go down there..

*Why don't you just use "rowid" instead?

SYNTAX :

CREATE MATERIALIZED VIEW LOG ON tableName
  [WITH [PRIMARY KEY][,ROWID]|[ROWID][,PRIMARY KEY] [(columnName[,...])]]

You see rowid there..

Specify the ROWID option to record the rowid of all changed rows. The ROWID option is useful when the table does not have a primary key or when you do not want to use the primary key when you create the materialized view.

Both rowid and primary key option is provided by Oracle and they act in the background and they work internally.. Something like an internal after row trigger is fired transparently.. So ...
When using rowid option , you need to make a full/complete refresh when the rowids of the rows are changed.. That is it.. That is the disadvantage.. But those rowids will change when you do a physical change.. like alter table move or simiar thing.. so you can go with rowids.. Ofcourse, you need to do your tests properly..

[ANIETO]

Good afternoon Erman, sorry, I was very busy and left this topic, but today I took it up again and when using the rowid I get the "ORA-12004: REFRESH FAST cannot be used for materialized view" error,

In Your example with  FND_USER Thats OK,
but in tables with ZD_EDITION_NAME field, by example AR.RA_TERMS_TL  I get error 12004.
I am about to think that I can not do Refresh FAST, just complete, but we have some cases that we need the FAST.

These are the steps I followed:

CREATE MATERIALIZED VIEW LOG ON AR.RA_TERMS_TL WITH ROWID;

CREATE OR REPLACE VIEW RA_TERMS_TL_LV#  
AS
SELECT *
FROM RA_TERMS_TL
;

BEGIN
APPS.AD_ZD_MVIEW.upgrade('APPS','RA_TERMS_TL_LV');
END;

exec DBMS_MVIEW.refresh('RA_TERMS_TL_LV','c'); -- Successfully
exec DBMS_MVIEW.refresh('RA_TERMS_TL_LV','f'); -- ERROR

ORA-12004: REFRESH FAST cannot be used for materialized view "APPS"."RA_TERMS_TL_LV"......


Thank you in advance

[ErmanArslansOracleBlog]

Hi,

It is an expected issue actually.. At least "sometimes" you may not be able to refresh the MVs using fast option. This is documented -> Developing and Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)

Ensure you are following the standards given in Section 2.3.6: Materialized View (MV)

Just use REFRESH FORCE' instead of 'REFRESH FAST'.  This will let ​materialized view to fall back to using a complete refresh in situations where the fast refresh is not possible.

Please see the following note for undestanding why a refresh fast may fail ;

Diagnosing ORA-12004 Refresh Fast Cannot be Used (Doc ID 179469.1)

From those reasons documented there, try to find the problem in your EBS 12.2 instance ( which leverages the EBR -- EBR seems the real cause bythway :)

So just do your analysis and udpate me.. I have currently no time to test or analyze(hands-on) this further, but if you update me, I can try to give you some ideas for solving this issue of yours.