Oracle spatial request working on one instance and not on another

Question

I have this statement that is generated by Geoserver

SELECT
shape AS shape
FROM
    (
        SELECT
            c.chantier_id      id,
            sdo_geom.sdo_buffer(c.shape, m.diminfo, 1) shape,
            c.datedebut        datedebut,
            c.datefin          datefin,
            o.nom              operation,
            c.brouillon        brouillon,
            e.code             etat,
            u.utilisateur_id   utilisateur,
            u.groupe_id        groupe
        FROM
            user_sdo_geom_metadata   m, lyv_chantier             c
            JOIN lyv_utilisateur          u ON c.createur_id = u.utilisateur_id
            JOIN lyv_etat                 e ON c.etat_id = e.etat_id
            JOIN lyv_operation            o ON c.operation = o.id
        WHERE
            m.table_name = 'LYV_CHANTIER'
            AND m.column_name = 'SHAPE'
    ) vtable
WHERE
    ( brouillon = 0
      AND ( etat != 'archive'
            OR etat IS NULL )
      AND sdo_filter(shape, mdsys.sdo_geometry(2003, 4326, NULL, mdsys.sdo_elem_info_array(1, 1003, 1), mdsys.sdo_ordinate_array(
      2.23365783691406, 48.665657043457, 2.23365783691406, 48.9341354370117, 2.76649475097656, 48.9341354370117, 2.76649475097656, 48.665657043457, 2.23365783691406, 48.665657043457)), 'mask=anyinteract querytype=WINDOW') = 'TRUE' );

On my local instance (dockerized if that can explain anything) it works fine, but on another instance I get an error :

ORA-13226: interface not supported without a spatial index

I guess that the SDO_FILTER is applied to the result of SDO_BUFFER which is therefore not indexed.
But why is it working on my local instance ?!
Is there some kind of weird configuration shenanigan that could explain the different behavior maybe ?

EDIT : The idea behind this is to get around a bug in Geoserver with Oracle databases where it renders only the first point of MultiPoint geometries, but works fine with MutltiPolygon. I am using a SQL view as layer in Geoserver (hence the subselect I guess).

Answer 1

First, you need to do some debugging here. Connect to each instance, on the same user as your Geoserver's datasource, and run the sql. From the same connections (in each instance) you must also verify that the user's metadata view (user_sdo_geom_metadata) have an entry for the table and the table has a spatial index - whose owner is the same user as the one you connect.
Also, your query ( select ... from 'vtable') has a column 'shape' which is a buffer of the column lyv_chantier.shape. The sdo_filter, in this sql, expects a spatial index on the vtable.shape - which cannot exist. You should try to use a different alias (e.g. buf_shape) and sdo_filter(buf_shape,...) - to see if the sql fails in both instances, as it should.

I'm in a bit of a hurry right now, so my instructions are summarized. If you want, do this debugging and post the results. We then can go into details.

EDIT: Judging from your efforts, I'd say that the simplest approach is: 1) add a second geometry column to lyv_chantier (e.g. buf_shp). 2) update lyv_chantier set buf_shp = sdo_geom.sdo_buffer(shape,...). 3) insert into user_sdo_geom_metadata the values (lyv_chantier, buf_shp, ...). 4) create a spatial index on column buf_shp. You may need to consider a trigger to update buf_shp whenever shape changes...
This is a very practical approach but you don't provide any info about your case (what is the oracle version, how many rows does the table have, how is it used, why do you want to use sdo_buffer, etc), so that's my recommendation for now.
Also, since you are, most likely, using an sql view as layer in Geoserver (you don't say anything about that, either), you could also consider using pure GS functionality to achieve your goal.
At the end, without describing your goal, it's difficult to provide anything more tailor-made.