The oracle documentation here:
Talks about creating an index in schema A with user B
CREATE INDEX t1_spatial_idx on A.T1(geometry) INDEXTYPE IS mdsys.spatial_index_v2;
This, however, requires you to insert into user_sdo_geom_metadata. I'm trying to do this as one single connected user and that seems impossible. Let's say I'm user B, whenever I insert into user_sdo_geom_metadata the resulting changes won't get picked up by the view that the CREATE INDEX statement uses, which is ALL_SDO_GEMO_METADATA. As an example try:
INSERT INTO user_sdo_geom_metadata (table_name,column_name,diminfo,srid) VALUES
('T1','geometry', sdo_dim_array(sdo_dim_element('X',-180.0,180.0, 0.005),sdo_dim_element('Y',-90.0,90.0, 0.005)), 4326);
There is no way to specify the schema to get this into the correct format. In fact, if the T1 table here doesn't exist in your schema then nothing appears in ALL_SDO_GEMO_METADATA. I've tried
ALTER SESSION SET CURRENT_SCHEMA = 'A'
But that doesn't work. I'm trying to use Schemas as a way of collating together self-contained groupings of tables, views, indexes, etc. like you would in a normal database. In other words, I'm trying to use them like actual schemas. Oracle makes this challenging at the best of times, and I don't want to manage separate logins for each schema, but I can't see how it's possible to do this for spatial indexes.
Well, this was an epic struggle but got there in the end. The only way to create the metadata AND the index as another user is to create a stored procedure that does the INSERT but it's not as easy as just following the instructions, you have to call the package proc directly like so:
CREATE OR REPLACE PROCEDURE "<user name>".ADDMETADATA(
schema_name VARCHAR2, table_name VARCHAR2, column_name VARCHAR2, srid NUMBER, bounds MDSYS.SDO_DIM_ARRAY)
IS
BEGIN
mdsys.sdo_meta.insert_all_sdo_geom_metadata(schema_name, table_name, column_name, bounds, srid);
END ADDMETADATA;
Which almost certainly isn't supported but the standard way is broken so you have to. You then need to create this in every single new schema along with giving the schema user CREATE TABLE and CREATE SEQUENCE permissions. So to create a schema programmatically and have it all work boils down to the following steps:
Painfully long-winded but works. The whole schema-as-user thing remains a terrible idea, most databases can do all this with one SQL statement.