This content has been marked as final. Show 3 replies
Dear Unnamed Person,
It looks like cross-schema issues to me.
You do all your work in the HHLT schema and then, at the end of your script you do this:
Yet in your post you are:
CREATE INDEX ELKORDY.BUILDING_SPATIAL_IDX ON ELKORDY.BUILDING(BUILDING_POLYGON) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
CREATE INDEX HHLT.BUILDING_SPATIAL_IDXSorry, which is it?
Also, when you select from the metadata table it tells you that the BUILDING table is in SYS schema (SYS is the owner) but not in HHLT or ELKORDY!
I suspect that:
alter session set current_schema = HHLT;Didn't connect as you expected - you are still the SYS user - so that when you did this:
It associated the metadata with the SYS owner and not the HHLT owner hence the error when creating the index.
INSERT INTO USER_SDO_GEOM_METADATA VALUES ('BUILDING', 'BUILDING_POLYGON', SDO_DIM_ARRAY( SDO_DIM_ELEMENT('X', 0, 820, 0.005), SDO_DIM_ELEMENT('Y', 0, 580, 0.005)), NULL);
I would recommend you.
1. Connect as the SYS schema.
2. Remove the metadata object;
3. Connect in a new session to the HHLT schema as the HHLT user;
5. Create a new metadata entry in user_sdo_geom_metadata when in HHLT schema;
6. Create the index (drop any existing broken index first).
Wow, I am a complete moron.
Connecting correctly as the HHLT user and running the script (and making sure everything is HHLT.[blah]) worked! You are awesome! Thank you for the explanation and for pointing out the correct method of do this.
And thanks for awarding the points: it is greatly appreciated.
Oh, no one's a moron. Sometimes we can't see the obvious.