Thank you so much Chris.

Thanks for the examples and links.

use below code to update materialized view when base table has having new rows.. this is only for updating materialized view from base table to materialized view.
create materialized view mv
here use on commit is refresh table automatically for mannual use on demand..
refresh fast with primary key for update on commit
as
select * from t;

update mv
set y = 3;

insert into mv values (3, 3);

select * from mv;

X Y
---------- ----------
1 3
2 3
3 3

select * from t;

X Y
---------- ----------
1 1
2 2

create materialized view on pre-built table.
create a trigger on the pre-built table
to capture inserts and updates.
and then the captured data could be updated or inserted
on the base table and then when mview is refreshed it wont be lost.
The inserts could be done thru batch or dbms_Scheduler on base.

dear Chris,
To keep the data in the underlying table and change them (in 11.2) use:
drop materialized view mv preserve table;
update mv set y = 3;
commit;
You reinstall the materialized view by adding 'on prebuilt table' as follows:
create materialized view mv on prebuilt table refresh fast with primary key as select * from t;
So, the mv is again not updatable.

Below is the whole procedure. Thanks for this great site.
prompt remove previous experiment (dropping the table mv probably is not needed)
drop table t;
drop materialized view mv;
drop table mv;
prompt as before

create table t ( x int primary key, y int );
insert into t values (1, 1);
insert into t values (2, 2);
commit;

create materialized view log on t including new values;

create materialized view mv refresh fast with primary key as select * from t;


prompt there are two database objects for mv:
select object_type from all_objects where object_name='MV';

prompt drop the materialized view, not the underlying table
drop materialized view mv preserve table;

prompt only the table for mv is left:
select object_type from all_objects where object_name='MV';

prompt adapt mv
update mv set y = 3;
commit;

prompt see what is in the table mv now:
select * from mv;

prompt reinstall the mv with exactly the same syntax and 'on prebuilt table' added:
create materialized view mv on prebuilt table refresh fast with primary key as select * from t;

prompt there are two database objects again for mv:
select object_type from all_objects where object_name='MV';

prompt the mv is again not updatable (error)
update mv set y = 4;


prompt remove all: as the materialized view has a prebuilt table, which needs to be removed separately:
drop materialized view mv;
drop table mv;
drop table t;

Team,

was looking into the product docs for the "refresh fast with primary key for update" by dont see that available.

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-MATERIALIZED-VIEW.html#GUID-EE262CA4-01E5-4618-B659-6165D993CA1B

kindly advice, where it is mentioned in the docs.

why materialized view does not have "replace" option?
any logic behind this?

Same reason a table does not create or replace.

Mviews are not simple views but have a table to hold the data as well (replication).

Hope it clarifies.

Cheers.

The ability of setting the MV to be updatable with the 'FOR UPDATE' clause made me think immediately of usage in unit tests, especially when using automatic rollback after each test case.

In those scenarios, you cannot see any data in MV without commit, even with fast-refresh so the only way to set up data would be using the FOR UPDATE in the MVs.

Ideally I would like this to be working only in development environment, not in production.
So I would like to modify my MVs in DEV so I can set them to be FOR UDPATE while the MVs in PROD are still 100% consistent and updates on them are not allowed

I tried using alter statement to set the for update option but is seems that it only works when creating an MV.

Working on DB version 19.7
I get: "

ORA-03001: unimplemented feature

"

Statement:

alter materialized view MV_TEST refresh COMPLETE on DEMAND for update

So is it not implemented or is there a way to do it witout rebuilding the whole MV?