My goal is to create a fast refreshable materialized view (MV) that holds all the combinations of records in specific related tables. So also the 'incomplete combinations'. So the query that is needed contains a full outer join.

First a disclaimer about using LiveSQL.
I tried but got the next message:
"Not Accepting New Users
Please use our V2 product, Oracle Free SQL. Free SQL allows you to learn and share SQL using the latest version of the Oracle database for free! https://freesql.com/"

In this text I lay out a complete case with alle create- and insert-statements needed (I hope it is sufficient).

Update on this disclaimer (20260204):
I started working on freesql (in stead of livesql) and I start to understand the meaning of it.
Small problem: the first statement creating a materialized view fails because of insufficient priviliges.
This happened in freesql db-version 19!
Update on this disclaimer (20260206):
I posted this in the freesql forum ( https://forums.oracle.com/ords/apexds/post/ora-01031-insufficient-privileges-when-i-try-to-create-a-ma-4031 ). The answer was: "REFRESH FAST ON COMMIT isn’t currently supported in FreeSQL, which is why you’re seeing an ORA-01031. I’ve logged a ticket with the dev team to look into supporting this going forward!"
I also tried it in freesql db-version 23: no problem. Even further: The whole problem with creating materialized view logs with the clause "commit scn" is gone and the materialized views are correctly fast refreshed on commit!
As long as I do not have more information, to me it looks like REFRESH FAST ON COMMIT in combination with 'commit scn' in the create materialized view log doesn't work well in version 19c.
I posted this on january 5th, I look forward to an answer.
Update on this disclaimer (20260209):
Next reaction on my question in the freesql-forum:
In Oracle 19c, creating a materialized view with REFRESH FAST ON COMMIT requires the ON COMMIT REFRESH system privilege. That privilege isn’t currently granted in the FreeSQL 19c environment, which is why you see ORA-01031 there.
This is why it works for you in FreeSQL 23ai. I’ve logged a ticket for us to review this difference and look into supporting this more consistently going forward. Thanks again for pointing this out!

Conclusion: It is (still) not possible to demonstrate this question in freesql.
</end disclaimer>

How to achieve my goal
It's not possible to code a full outer join directly into a MV, that I know.
So my plan is this:
1) Every left or right outer join concerning the aforementioned tables is put into a MV with the (+)-notation.
2) These MV's can be used in next MV's.
3) The full outer join (the last step in the example I will give) is implemented as:
a) a MV holding the left outer join
b) a MV holding the right outer join
c) a MV that will 'union all' these two MV's together
d) a MV that picks out the unique records from this union-all-MV
This cannot be done with a 'distinct', but should be possible with a group by.
( https://connor-mcdonald.com/2020/02/11/group-by-might-be-distinctly-better-than-distinct/#:~:text=For%20example%2C%20in%20most%20circumstances,the%20ire%20of%20the%20database )

I tested step 3d already, but not in the complete construction which I layed out above. But it seem to work.
In the above construction I managed up and until step 3b, all these MV's seem to work and are refreshed when I do something to the data in the base tables.
The MV in step 3c is created without any complaint or error message. But when I edit the data in one of the base tables it is not refreshed. And I can't see why.

So my question is:
- Is my plan feasible?
Or is the plan based on a fallacy and will never work, whatever I do?
- If 'yes, it is feasible', then what am I doing wrong? Why is the union-all-MV not correctly fast refreshed?

I created an example that is equivalent to -the for this question relevant part of- the situation in our database, but stripped down to as least columns as possible.

This is where, in the case I lay out, the problem manifests:
The MV t_mv_acd_f_pre_b is built, with the right records in it, but when I mutate data in the some base table it is not refreshed.
When I run the query with the union all outside the MV t_mv_acd_f_pre_b (but with use off all other MV's) I see the mutations that I did to the data in the base tables. But I do not see these mutations in the MV t_mv_acd_f_pre_b.

UPDATE
Meanwhile, a colleague and I investigated this case further.
We reasoned that the two tables combined in the union all have the same source (the MV t_mv_ac_l_d and the table t_b). This led us to believe that some kind of SCN collision could be occurring in the union all.
Therefore, we omitted the 'commit scn' from the materialized view logs t_mv_acd_l_b and t_mv_acd_r_b.
And now it works. Mutations to data in the base tables are propagated up to and including the MV with the union all.
This changes my question:
Can you confirm this diagnosis?
If 'yes', can you explain better and/or in further detail then just 'SCN collision'?
Or is there still something else going on?
EXTRA on 20260204:
Is this a problem specific with db-version 19?
In freesql db-version 23 it all seems to work out fine.

ERD:

 +----------------------+ +---------------+
 |  t_a                 | |  t_b          |
 |                      | |               |
 |  #* a_k              | |  #* b_k       |
 |   o a_p              | |   o b_p       |
 |                      | |               |
 +----------------------+ +---------------+
    |                |      |
    |  identifying   |      | relation
    |  relations     |      | not
    |                |      | identifying
    /\              /\     /\
 +-----------+ +--------------------------+
 |  t_c      | |  t_d                     |
 |           | |                          |
 |  #* c_k   | |  #* d_k                  |
 |   o c_p   | |   o d_p                  |
 |           | |                          |
 +-----------+ +--------------------------+

Yes, our database uses inherited (meaningfull) keys instead of meaningless id's!
That is a fact we cannot change in the foreseeable future.

First, the tables:

create table t_a(a_k varchar2(5) not null                                                    , a_p varchar2(5));
create table t_b(b_k varchar2(5) not null                                                    , b_p varchar2(5));
create table t_c(a_k varchar2(5) not null, c_k varchar2(5) not null                          , c_p varchar2(5));
create table t_d(a_k varchar2(5) not null, d_k varchar2(5) not null, b_k varchar2(5) not null, d_p varchar2(5));

alter table t_a add constraint t_a_pk     primary key (a_k);
alter table t_b add constraint t_b_pk     primary key (b_k);
alter table t_c add constraint t_c_pk     primary key (a_k, c_k);
alter table t_d add constraint t_d_pk     primary key (a_k, d_k);

alter table t_c add constraint t_c_a_fk01 foreign key (a_k) references t_a (a_k);
alter table t_d add constraint t_d_a_fk01 foreign key (a_k) references t_a (a_k);
alter table t_d add constraint t_d_b_fk01 foreign key (b_k) references t_b (b_k);

Then the contents of the tables:

insert into t_a values ('ak1', 'ap1');
insert into t_a values ('ak2', 'ap2');
insert into t_a values ('ak3', 'ap3');
insert into t_a values ('ak4', 'ap4');
insert into t_a values ('ak5', 'ap5');
insert into t_a values ('ak6', 'ap6');

insert into t_b values ('bk1', 'bp1');
insert into t_b values ('bk2', 'bp2');
insert into t_b values ('bk3', 'bp3');
insert into t_b values ('bk4', 'bp4');

insert into t_c values ('ak1', 'ck1', 'cp1');
insert into t_c values ('ak2', 'ck2', 'cp2');
insert into t_c values ('ak3', 'ck3', 'cp3');
insert into t_c values ('ak4', 'ck4', 'cp4');

insert into t_d values ('ak1', 'dk1', 'bk1', 'dp1');
insert into t_d values ('ak2', 'dk2', 'bk1', 'dp2');
insert into t_d values ('ak3', 'dk3', 'bk2', 'dp3');
insert into t_d values ('ak5', 'dk5', 'bk3', 'dp5');

So the final goal is to have a MV that holds these records:

select ac_l_d.a_k
,      ac_l_d.a_p
,      ac_l_d.c_k
,      ac_l_d.c_p
,      ac_l_d.d_k
,      ac_l_d.d_p
,      nvl(ac_l_d.b_k, b.b_k) as b_k
,      b.b_p
from   (  select a_l_c.a_k
          ,      a_l_c.a_p
          ,      a_l_c.c_k
          ,      a_l_c.c_p
          ,      d.d_k
          ,      d.d_p
          ,      d.b_k
          from   (  select a.a_k
                    ,      a.a_p
                    ,      c.c_k
                    ,      c.c_p
                    from   t_a     a
                    left   outer
                    join   t_c     c
                    on     a.a_k = c.a_k
                 )                 a_l_c
          left   outer
          join   t_d               d
          on          a_l_c.a_k  = d.a_k
       )                  ac_l_d
full   outer
join   t_b                b
on          ac_l_d.b_k  = b.b_k
order
by     ac_l_d.a_k
,      ac_l_d.c_k
,      ac_l_d.d_k
,      nvl(ac_l_d.b_k, b.b_k)
;

+-----+-----+-----+-----+-----+-----+-----+-----+
| A_K | A_P | C_K | C_P | D_K | D_P | B_K | B_P |
+-----+-----+-----+-----+-----+-----+-----+-----+
| ak1 | ap1 | ck1 | cp1 | dk1 | dp1 | bk1 | bp1 |
| ak2 | ap2 | ck2 | cp2 | dk2 | dp2 | bk1 | bp1 |
| ak3 | ap3 | ck3 | cp3 | dk3 | dp3 | bk2 | bp2 |
| ak4 | ap4 | ck4 | cp4 |     |     |     |     |
| ak5 | ap5 |     |     | dk5 | dp5 | bk3 | bp3 |
| ak6 | ap6 |     |     |     |     |     |     |
|     |     |     |     |     |     | bk4 | bp4 |
+-----+-----+-----+-----+-----+-----+-----+-----+

These are the MV's I created:

create materialized view log on t_a with primary key, rowid, sequence, commit scn (a_p) including new values;
create materialized view log on t_b with primary key, rowid, sequence, commit scn (b_p) including new values;
create materialized view log on t_c with primary key, rowid, sequence, commit scn (c_p) including new values;
create materialized view log on t_d with primary key, rowid, sequence, commit scn (d_p) including new values;

------------------------------------
--
--  a left outer c
--
------------------------------------
create     materialized
view       t_mv_a_l_c
refresh    fast
on         commit
as
select a.a_k
,      a.a_p
,      c.c_k
,      c.c_p
--
,      cast(
             a.a_k
          || c.c_k
       as varchar2(10)
       )                        as pk_a_l_c
--
,      c.a_k                    as mvk_a_a_k
--
,      a.rowid                  as mvr_a
,      c.rowid                  as mvr_c
from   t_a     a
,      t_c     c
where  1 = 1
and    a.a_k = c.a_k(+)
;

alter
table      t_mv_a_l_c
add
constraint t_mv_a_l_c_pk
primary    key(pk_a_l_c)
;

create     materialized
view       log
on         t_mv_a_l_c
with       primary key
,          rowid
,          sequence
,          commit scn
           ( a_k
           , a_p
           , c_k
           , c_p
           )
including  new values;

------------------------------------
--
-- (a left outer c) left outer d
--
------------------------------------
create     materialized
view       t_mv_ac_l_d
refresh    fast
on         commit
as
select a_l_c.a_k
,      a_l_c.a_p
,      a_l_c.c_k
,      a_l_c.c_p
,          d.d_k
,          d.b_k
,          d.d_p
--
,      cast(
             a_l_c.a_k
          || a_l_c.c_k
          || d.d_k
       as varchar2(15)
       )                        as pk_ac_l_d
--
,          d.a_k                as mvk_d_a_k
--
,      a_l_c.rowid              as mvr_ac
,          d.rowid              as mvr_d
from   t_mv_a_l_c        a_l_c
,      t_d               d
where  1 = 1
and         a_l_c.a_k  = d.a_k(+)
;

alter
table      t_mv_ac_l_d
add
constraint t_mv_ac_l_d_pk
primary    key(pk_ac_l_d)
;

create     materialized
view       log
on         t_mv_ac_l_d
with       primary key
,          rowid
,          sequence
,          commit scn
           ( a_k
           , a_p
           , c_k
           , c_p
           , d_k
           , b_k
           , d_p
           )
including  new values;

------------------------------------
--
-- ((a left outer c) left outer d) left outer b
--
------------------------------------

create     materialized
view       t_mv_acd_l_b
refresh    fast
on         commit
as
select ac_l_d.a_k
,      ac_l_d.a_p
,      ac_l_d.c_k
,      ac_l_d.c_p
,      ac_l_d.d_k
,      ac_l_d.d_p
,      ac_l_d.b_k
,           b.b_p
--
,      cast(
             ac_l_d.a_k
          || ac_l_d.c_k
          || ac_l_d.d_k
          || ac_l_d.b_k
          || 'l'
        as varchar2(21)
       )                        as pk_acd_l_b
--
,      b.b_k                    as mvk_b_b_k
--
,      ac_l_d.rowid             as mvr_acd
,           b.rowid             as mvr_b
from   t_mv_ac_l_d        ac_l_d
,      t_b                b
where  1 = 1
and         ac_l_d.b_k  = b.b_k(+)
;

alter
table      t_mv_acd_l_b
add
constraint t_mv_acd_l_b_pk
primary    key(pk_acd_l_b)
;

create     materialized
view       log
on         t_mv_acd_l_b
with       primary key
,          rowid
,          sequence
,          commit scn
           ( a_k
           , a_p
           , c_k
           , c_p
           , d_k
           , d_p
           , b_k
           , b_p
           )
including  new values;

------------------------------------
--
-- ((a left outer c) left outer d) right outer b
--
------------------------------------

create     materialized
view       t_mv_acd_r_b
refresh    fast
on         commit
as
select ac_l_d.a_k
,      ac_l_d.a_p
,      ac_l_d.c_k
,      ac_l_d.c_p
,      ac_l_d.d_k
,      ac_l_d.d_p
,           b.b_k
,           b.b_p
--
,      cast(
             ac_l_d.a_k
          || ac_l_d.c_k
          || ac_l_d.d_k
          ||      b.b_k
          || 'r'
        as varchar2(21)
       )                        as pk_acd_r_b
--
--
,      ac_l_d.rowid             as mvr_acd
,           b.rowid             as mvr_b
from   t_mv_ac_l_d           ac_l_d
,      t_b                   b
where  1 = 1
and         ac_l_d.b_k(+)  = b.b_k
;

alter
table      t_mv_acd_r_b
add
constraint t_mv_acd_r_b_pk
primary    key(pk_acd_r_b)
;

create     materialized
view       log
on         t_mv_acd_r_b
with       primary key
,          rowid
,          sequence
,          commit scn
           ( a_k
           , a_p
           , c_k
           , c_p
           , d_k
           , d_p
           , b_k
           , b_p
           )
including  new values;

------------------------------------
--
-- ((a left outer c) left outer d) full outer b  [ first part, the union all ]
--
------------------------------------
create     materialized
view       t_mv_acd_f_pre_b
refresh    fast
on         commit
as
select a_k
,      a_p
,      c_k
,      c_p
,      d_k
,      d_p
,      b_k
,      b_p
--
,      'l'                      as marker
--
,      pk_acd_l_b               as pk_acd_f_pre_b
--
--
,      rowid                    as mvr_acd_f_pre_b
from   t_mv_acd_l_b
union  all
select a_k
,      a_p
,      c_k
,      c_p
,      d_k
,      d_p
,      b_k
,      b_p
--
,      'r'                      as marker
--
,      pk_acd_r_b               as pk_acd_f_pre_b
--
--
,      rowid                    as mvr_acd_f_pre_b
from   t_mv_acd_r_b
;

alter
table      t_mv_acd_f_pre_b
add
constraint t_mv_acd_f_pre_b_pk
primary    key(pk_acd_f_pre_b)
;