0

EGDB: Oracle 18c/10.7.1

External system: Oracle 12c

I have created a non-spatial materialized view in an EGDB that syncs data from an external system (on a 5 second schedule; via a dblink).

  • It is not possible to create an on-commit materialized view over a dblink. So I used a schedule instead.
create materialized view my_gis_schema.wo_mv --EGDB
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
    maximo.workorder@gistomax --dblink

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

The materialized view works as expected. Records that are new/modified/deleted in the external system are synced to the materialized view on a 5 second schedule.

Problem:

As an experiment, I registered the materialized view with the geodatabase (via ArcGIS Desktop).

The view was still useable in ArcMap as an XY layer, however, it failed to sync after that point. And any attempt to manually refresh, delete, or otherwise modify the view failed (the process would just hang, and the MV would keep reappearing in Catalog after deletion).

Why does registering an MV with the GDB break the MV?

Improve this question
2
  • 1
    If you're going to go materialized view, you should keep going, all the way to including geometry creation (though 5 seconds seems far too expensive for that), then NOT register, and instead use it as a Query Layer. You should look at the mv contents to see what changes were made after registration, but I wouldn't expect this to work without a real NOT NULL rowid. – Vince Nov 24 '20 at 19:33
  • @Vince Thanks. I recreated the MV. 1) I added an SDO_GEOM column and added a spatial index via a GP tool. 2) I decreased the sync interval to 30 seconds. 3) I won't register MVs with the GDB in the future (breaks the MV). 4) I added a NOT NULL constraint to the OBJECTID column (since MVs are really just tables). 5) While ArcMap doesn't recognize the OBJECTID as a proper OBJECTID, ArcGIS Pro does recognize it just fine. And likewise, the map service recognizes the OBJECTID too. So it's all working very well now! – User1973 Nov 27 '20 at 20:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.