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: db&lt;&gt;fiddle.
CREATE OR REPLACE TYPE SDE.&quot;ST_GEOMETRY&quot; (
...
) 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:
&quot;ORA-30373: object data types are not supported in this context&quot;.

Code:
SQL&gt; 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:
&quot;6482996.992 - ORA-30373 MATERIALIZE VIEW UNABLE TO REPLICATE A TYPE
WHICH CONTAINS SUBTYPES. Enhancement Request (ER) 6370112&quot;.

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 &quot;object column&quot;).

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?
I tested in my test server ambient and worked.
SQL&gt; create table test_mv (col1 number not null unique, col2 number);

Table created.

SQL&gt; create materialized view log on test_mv with primary key;

Materialized view log created.

SQL&gt;  create materialized view mv refresh fast on demand as
  2     select col1,col2, sdo_geometry(2001, 26917, sdo_point_type(col1,col2, null), null, null) as shape
  3     from test_mv;

Materialized view created.

SQL&gt; insert into test_mv values (1, 1);

1 row created.

SQL&gt;  insert into test_mv values (2, 2);

1 row created.

SQL&gt; commit;

Commit complete.

SQL&gt; exec dbms_mview.refresh('MV', 'f');

PL/SQL procedure successfully completed.

col1 col2 shape
1   1   (2001, 26917, (1, 1, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))
2   2   (2001, 26917, (2, 2, ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ), (, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ))