The Materialized View is a non-editioned object type, and therefore a materialized view cannot directly reference editioned objects. To avoid this
limitation, Oracle E-Business Suite Online Patching technology implements a new Effectively Editioned Materialized View compound object. Application developers
create and maintain the Materialized View Definition (query) in an ordinary view. The Online Patching technology then automatically maintains a
corresponding Materialized View Implementation that is legal for editioned databases.
MV Definition Standards:
A Materialized View Name must be unique within the first 29 bytes.
A Materialized View Definition must be stored in an ordinary view called MV_NAME||'#'.
Create or replace the Materialized View Definition as an ordinary view calledmv_name||'#'.
Test the MV Definition for accuracy before generating the MV Implementation.
For example:
create or replace view FND_EXAMPLE_MV# as select ... ;
select * from fnd_example_mv#;
The Materialized View Implementation is automatically generated from the MV Definition using the AD_ZD_MVIEW.UPGRADE procedure.
The syntax is exec ad_zd_mview.upgrade(<MV_OWNER>, <MV_NAME>)
Do not attempt to directly create or replace the Materialized View Implementation Query. To recreate an MV Implementation, call the
AD_ZD_MVIEW.UPGRADE procedure.
A Materialized View Definition must specify a column alias for each item in the select list.
In this scenario how we need to make this work for custom materialized views?
limitation, Oracle E-Business Suite Online Patching technology implements a new Effectively Editioned Materialized View compound object. Application developers
create and maintain the Materialized View Definition (query) in an ordinary view. The Online Patching technology then automatically maintains a
corresponding Materialized View Implementation that is legal for editioned databases.
MV Definition Standards:
A Materialized View Name must be unique within the first 29 bytes.
A Materialized View Definition must be stored in an ordinary view called MV_NAME||'#'.
Create or replace the Materialized View Definition as an ordinary view calledmv_name||'#'.
Test the MV Definition for accuracy before generating the MV Implementation.
For example:
create or replace view FND_EXAMPLE_MV# as select ... ;
select * from fnd_example_mv#;
The Materialized View Implementation is automatically generated from the MV Definition using the AD_ZD_MVIEW.UPGRADE procedure.
The syntax is exec ad_zd_mview.upgrade(<MV_OWNER>, <MV_NAME>)
Do not attempt to directly create or replace the Materialized View Implementation Query. To recreate an MV Implementation, call the
AD_ZD_MVIEW.UPGRADE procedure.
A Materialized View Definition must specify a column alias for each item in the select list.
In this scenario how we need to make this work for custom materialized views?
Hello,
ReplyDeleteWe are in the process of upgrading to R12.2 from R12.1.3
This is a question regarding what needs to be done to the existing custom materialized views BEFORE upgrading to 12.2.
I think the steps which you have mentioned above are for an existing R12.2 ebs database.
What to do for to prepare custom mviews before the upgrade process to R12.2 ?
Thanks
ReplyDeletePlease go through the section "Enabling Online Patching" in below doc.
https://docs.oracle.com/cd/E26401_01/doc.122/e48839/T579510T580325.htm