Materialized Views in 12.2.X

 There are two steps to create Materialized Views in 12.2

1. Create Logical View
2. Upgrade to Materialized View

Create Logical View
CREATE OR REPLACE <Custom Schema>.XXBLOG_CREATE_LOGICAL_MV#
AS
<SELECT Statement>;


Upgrade Logical View to Materialized View
exec ad_zd_mview.upgrade('<custom schema>', 'XXBLOG_CREATE_LOGICAL_MV');

How to check if the Materialized Views got created properly in 12.2
SELECT object_type, object_name
FROM dba_objects
WHERE object_name like 'XXBLOG_CREATE_LOGICAL_MV%';

-- 4 rows Selected

These 4 Rows get created in the process for

1. Logical View (Ending with '#') in Custom Schema
2. Materialized View in Custom Schema
3. Table in Custom Schema
4. Synonym in APPS

Possible Issue:
Create mview gives the following error when the mview definition query refers to editioned function:
ORA-38818: illegal reference to editioned object %.%
Possible Solution:
Re-create the referenced pl/sql objects in a schema where editioning is not turned on
If the referenced objects are be placed in a non editioned schema that is different from the schema where the mview is to be created, then grant execute permission on the package to the mview owner.References:
12.2 E-Business Suite Upgrade Of Custom Materialized Views Fails Due To Changes Required For 12.2 ADOP Editioned Objects (Doc ID 2205375.1)
ORA-38818: Illegal Reference To Editioned Object When Creating Materialized View (Doc ID 1556379.1)

How to Refresh Materialized Views:
exec DBMS_MVIEW.REFRESH('XXBLOG_HOWTOREFRESH_MV', method => '?',  atomic_refresh => FALSE, out_of_place => TRUE);


Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes by incrementally applying changes to the materialized view.
		For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT	P	Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE	?	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

Out-of-place refresh: It uses outside tables during refresh as opposed to the existing "in-place" refresh that directly applies changes to the materialized view container table. Out-of-place refresh is particularly effective when handling situations with large amounts of data changes, where conventional DML statements do not scale well.