Oracle MV requires object type to be defined as FINAL?

I want to create an Oracle 18c **materialized view (MV)** on a table that has a user-defined datatype called ST_GEOMETRY : - The MV would do a COMPLETE refresh on a schedule — on a local table. ------------ **Some info about ST_GEOMETRY:** > The ST_Geometry data type implements the SQL 3 specification of > user-defined data types (UDTs), allowing you to create columns capable > of storing spatial data such as the location of a landmark, a street, > or a parcel of land. It provides International Organization for > Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant > structured query language (SQL) access to the geodatabase and > database. This storage extends the capabilities of the database by > providing storage for objects (points, lines, and polygons) that > represent geographic features. It was designed to make efficient use > of database resources; to be compatible with database features such as > replication and partitioning; and to provide rapid access to spatial > data. > > ST_Geometry itself is an abstract, noninstantiated superclass. > However, its subclasses can be instantiated. An instantiated data type > is one that can be defined as a table column and have values of its > type inserted into it. The SQL definition of the user-defined datatype can be found here: dbfiddle . CREATE OR REPLACE TYPE SDE."ST_GEOMETRY" ( ... ) NOT final *(Although, I'm guessing most of the logic is stored in the EXTPROC.)* ----------------- **MV object types must be FINAL:** There is a known issue when trying to create an MV on the ST_GEOMETRY user-defined datatype (ORA-30373 ): > Creating an Oracle materialized view for a table containing an > ST_Geometry attribute returns the following error: > > "ORA-30373: object data types are not supported in this context". > > Code: > SQL> CREATE MATERIALIZED VIEW parcel_view > 2 AS SELECT * FROM parcel@remote_server; > > CREATE MATERIALIZED VIEW parcel_view > * > ERROR at line 1: > ORA-30373: object data types are not supported in this context > > Cause: > > Oracle's Advanced Replication functionality requires that **all** > **object types be defined as FINAL** to participate within a materialized > view. > The reason that the ST_Geometry cannot be defined as FINAL is because > the type contains subtypes used for type inheritance. Oracle does not > allow a type with subtypes to be defined as FINAL. > > Esri is currently working with Oracle to address this issue and > limitation. The following Oracle TAR file is available for reference: > "6482996.992 - ORA-30373 MATERIALIZE VIEW UNABLE TO REPLICATE A TYPE > WHICH CONTAINS SUBTYPES. Enhancement Request (ER) 6370112". *Note: I've contacted ESRI support and they say there hasn't been any progress with Oracle regarding the enhancement request.* ------------- **Question:** Is there a way to workaround this issue? - I ask because ESRI has a reputation of not being great with Oracle. So there is a chance that they have misunderstood the issue or haven't gotten creative enough. ------------ **For example, what about the ONLY keyword?** 9.9 Materialized View Support for Objects > For both object-relational and object materialized views that are > based on an object **table**, if the type of the master object table > is not FINAL, the FROM clause in the materialized view definition > query must include the ONLY keyword. > > For example: > > CREATE MATERIALIZED > VIEW customer OF cust_objtyp AS > SELECT CustNo FROM ONLY HR.Customer_objtab@dbs1; > > Otherwise, the FROM clause must omit the ONLY keyword. Notes: - Unfortunately, that blurb is about object **tables**, not about object **columns** (I believe the ST_GEOMETRY user-defined datatype would be considered an "object column"). - But I wonder if the concept of the ONLY keyword could be used to help with the FINAL problem with the ST_GEOMETRY object column. Any ideas?