Creating a Cross-Schema Spatial Index in Oracle impossible with single user?

mxcolin 0
Viewed 43

The oracle documentation here:

https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/indexing-querying-spatial-data.html#GUID-8E6AE949-758B-4A5E-9453-CC3D00647497

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.

oracle-spatial spatial spatial-index oracle
1 Answers

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:

  • CREATE the Schema/User and set the tablespace
  • GRANT CREATE SEQUENCE to the Schema/User
  • GRANT CREATE TABLE to the Schema/User
  • ALTER the quota on the TABLESPACE for the Schema/User
  • CREATE the stored proc above
  • CALL the stored proc
  • CALL create INDEX

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.

mxcolin0
Related