3 Replies Latest reply: Mar 4, 2013 12:44 AM by Simon Greener RSS

    Issues Creating Spatial Index (Beginner)

    994501
      I am getting the error below:

      CREATE INDEX HHLT.BUILDING_SPATIAL_IDX
      *
      ERROR at line 1:
      ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
      ORA-13203: failed to read USER_SDO_GEOM_METADATA view
      ORA-13203: failed to read USER_SDO_GEOM_METADATA view
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10


      When I select run the following, I get:
      select * from mdsys.sdo_geom_metadata_table;

      SDO_OWNER SDO_TABLE_NAME
      -------------------------------- --------------------------------
      SDO_COLUMN_NAME
      --------------------------------------------------------------------------------

      SDO_DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
      --------------------------------------------------------------------------------

      SDO_SRID
      ----------
      SYS BUILDING
      BUILDING_POLYGON
      SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 0, 820, .005), SDO_DIM_ELEMENT('Y', 0, 580, .

      005))



      This is my script:
      =========================
      create user HHLT identified by HHLT;
      grant all privileges to HHLT;

      alter session set current_schema = HHLT;

      CREATE TABLE HHLT.BUILDING
      (
      BUILDING_ID VARCHAR2(3),
      BUILDING_NAME VARCHAR2(50),
      VERTICES_COUNT INT,
      BUILDING_POLYGON MDSYS.SDO_GEOMETRY,
      CONSTRAINT BUILDING_PK PRIMARY KEY (BUILDING_ID)
      );

      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);

      COMMIT;

      CREATE INDEX ELKORDY.BUILDING_SPATIAL_IDX
      ON ELKORDY.BUILDING(BUILDING_POLYGON)
      INDEXTYPE IS MDSYS.SPATIAL_INDEX;
      ==================================

      What am I missing? Working from Windows 7 install.
        • 1. Re: Issues Creating Spatial Index (Beginner)
          Simon Greener
          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:
          CREATE INDEX ELKORDY.BUILDING_SPATIAL_IDX
           ON ELKORDY.BUILDING(BUILDING_POLYGON)
           INDEXTYPE IS MDSYS.SPATIAL_INDEX;
          Yet in your post you are:
          CREATE INDEX HHLT.BUILDING_SPATIAL_IDX
          Sorry, 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:
          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);
          It associated the metadata with the SYS owner and not the HHLT owner hence the error when creating the index.

          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).

          regards
          SImon
          • 2. Re: Issues Creating Spatial Index (Beginner)
            994501
            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.
            • 3. Re: Issues Creating Spatial Index (Beginner)
              Simon Greener
              My pleasure.

              And thanks for awarding the points: it is greatly appreciated.

              Oh, no one's a moron. Sometimes we can't see the obvious.

              regards
              Simon