Create materialized view with NOT NULL column? (dblink to external table)

Question

I've created a materialized view that syncs data from an external system (on a 5 second schedule; via a dblink).

• The primary system that has the MV is Oracle 18c.
• The external system with the source data/dblink is Oracle 19c

---

create materialized view log on my_external_sys.workorder with rowid;  --external system 

create materialized view my_primary_sys.wo_mv --primary system 
build immediate 
refresh force 
on demand 
start with sysdate next sysdate + (5/(60*60*24))
as
select
    cast(workorderid as number(38,0)) as objectid,
    wonum,
    status,
    --other fields
    longitudex,
    latitudey
from
    my_external_sys.workorder@gistomax --dblink

The materialized view works as expected.

However, I realize now that my primary system has special requirements for its tables (or, in this case, MVs).

• There needs to be a column in tables/MVs called OBJECTID that has the NOT NULL constraint.

• More info here: SDO_GEOMETRY and ArcGIS

---

Question:

Is there a way to create a materialized view (via a dblink to an external db/table) that has a column with the NOT NULL constriant?

Answer 1

I made the mistake of assuming that alter table wouldn't work on an MV.

But it totally works.

alter table my_primary_sys.wo_mv modify objectid not null;

That was easy.