4 Replies Latest reply: Jun 29, 2012 11:15 AM by Ying Hu-Oracle RSS

    11gR2 - ORA-13268: error obtaining dimension from USER_SDO_GEOM_METADATA

    908083
      Hi,

      I'm getting the above error when running a SDO_Within_Distance query, SDO_Geom_Metadata is populated correctly for the table, so why would the error be thrown that indicates it's missing?

      Here's my reproducable test case. The query that shows the error looks a bit basic and not very useful, but has been stripped down to the simplest form that generates the error. Any help appreciated.



      Environment = Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      OS = Oracle Enterprise Linux Server release 5.2 (Carthage) 2.6.18 92.el5 (32-bit)

      Here's my repeatable test-case which generates the error...

      -- Tablespaces used for test case...
      -- Event_ID_01_TS
      -- Event_ID_02_TS
      -- Event_1_TS
      -- Event_2_TS
      -- Event_Default_TS

      CONNECT Sys/...@OEL5DB AS SysDBA

      DROP USER TEST_USER CASCADE;
      CREATE USER TEST_USER IDENTIFIED BY x DEFAULT TABLESPACE Users TEMPORARY TABLESPACE Temp ACCOUNT UNLOCK;

      GRANT CONNECT TO TEST_USER;
      GRANT RESOURCE TO TEST_USER;
      GRANT CREATE TABLE TO TEST_USER;

      CONNECT TEST_USER/x@OEL5DB

      CREATE TYPE Partition_type AS OBJECT
      (
      ID INTEGER,
      eventDate Timestamp(3),
      eventLocation MDSys.SDO_Geometry
      )
      /

      -- Range Partitioned Object Table, uses Hash-Partitioned Global PK index
      CREATE TABLE Event_Table
      OF Partition_Type (PRIMARY KEY (ID) USING INDEX
      GLOBAL PARTITION BY HASH (ID) PARTITIONS 2
      STORE IN
      (
      Event_ID_01_TS,
      Event_ID_02_TS
      )
      STORAGE
      (
      INITIAL 1M PCTINCREASE 0 NEXT 1M
      ) NOLOGGING
      )
      OBJECT IDENTIFIER IS PRIMARY KEY
      PARTITION BY RANGE(eventDate)
      (
      PARTITION Event_Part_1 VALUES LESS THAN (To_Date('07-Feb-2012','DD-Mon-YYYY')) TABLESPACE Event_1_TS,
      PARTITION Event_Part_2 VALUES LESS THAN (To_Date('08-Feb-2012','DD-Mon-YYYY')) TABLESPACE Event_2_TS,
      PARTITION Event_Default VALUES LESS THAN (MAXVALUE) TABLESPACE Event_Default_TS
      )
      PCTFREE 0
      STORAGE (INITIAL 1M
      PCTINCREASE 0
      NEXT 1M);

      ALTER TABLE Event_Table ADD CONSTRAINT Event_Table_CC1
      CHECK (eventDate IS NOT NULL AND
      eventLocation IS NOT NULL AND
      eventLocation.SDO_Point.X IS NOT NULL AND
      eventLocation.SDO_Point.Y IS NOT NULL AND
      eventLocation.SDO_Point.Z IS NULL);

      CREATE INDEX Event_Table_I1 ON Event_Table (eventDate)
      LOCAL
      (
      PARTITION Event_Part_1 TABLESPACE Event_1_TS PCTFREE 0 STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K),
      PARTITION Event_Part_2 TABLESPACE Event_2_TS PCTFREE 0 STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K),
      PARTITION Event_Default TABLESPACE Event_Default_TS PCTFREE 0 STORAGE (INITIAL 1K PCTINCREASE 0 NEXT 1K)
      )
      TABLESPACE Event_Default_TS STORAGE (INITIAL 1K PCTINCREASE 0 NEXT 1K) NOLOGGING PARALLEL;

      INSERT INTO User_SDO_Geom_Metadata
      (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
      VALUES
      (
      'Event_Table',
      'eventLocation',
      SDO_DIM_ARRAY
      (
      SDO_DIM_ELEMENT('Longitude',-180,180,1),
      SDO_DIM_ELEMENT('Latitude', -90, 90,1)
      ),4326);

      -- Create partitioned spatial index...
      CREATE INDEX Event_Table_SI1
      ON Event_Table(eventLocation)
      INDEXTYPE IS MDSYS.SPATIAL_INDEX
      PARAMETERS
      ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_Default_TS Initial=1K PctIncrease=0 Next=1K')
      LOCAL
      (
      PARTITION Event_Part_1 PARAMETERS ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_1_TS Initial=1M PctIncrease=0 Next=512K'),
      PARTITION Event_Part_2 PARAMETERS ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_2_TS Initial=1M PctIncrease=0 Next=512K'),
      PARTITION Event_Default PARAMETERS ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_Default_TS Initial=1K PctIncrease=0 Next=1K')
      ) PARALLEL;

      ALTER TABLE Event_Table DROP PARTITION Event_Default;

      -- Create some test data...
      declare
      event partition_type;
      BEGIN

      DELETE Event_Table;

      -- 1st Partition data...
      event := new Partition_Type (1, To_Date('06-Feb-2012 12:00:00','DD-Mon-YYYY HH24:MI:SS'),
      SDO_Geometry
      (2001, 4326,SDO_Point_Type(10, 10, Null), Null,Null));
      INSERT INTO Event_Table VALUES event;

      event := new Partition_Type (2, To_Date('06-Feb-2012 13:00:00','DD-Mon-YYYY HH24:MI:SS'),
      SDO_Geometry
      (2001, 4326,SDO_Point_Type(20, 20, Null), Null,Null));
      INSERT INTO Event_Table VALUES event;

      -- 2nd Partition data...
      event := new Partition_Type (3, To_Date('07-Feb-2012 14:00:00','DD-Mon-YYYY HH24:MI:SS'), -- sysdate,
      SDO_Geometry
      (2001, 4326,SDO_Point_Type(30, 30, Null), Null,Null));
      INSERT INTO Event_Table VALUES event;

      event := new Partition_Type (4, To_Date('07-Feb-2012 15:00:00','DD-Mon-YYYY HH24:MI:SS'), -- sysdate,
      SDO_Geometry
      (2001, 4326,SDO_Point_Type(40, 40, Null), Null,Null));
      INSERT INTO Event_Table VALUES event;

      COMMIT;

      END;
      /

      ---------------------------------------------------------------------------------------------------------------
      -- now run sql that produces error...
      SELECT E1.ID, E2.ID
      FROM (SELECT E.EventLocation,
      E.ID,
      E.EventDate
      FROM Event_Table E) E1,
      Event_Table E2
      WHERE (SDO_Within_Distance(E2.EventLocation, E1.EventLocation, 'distance=1000 unit=meter') = 'TRUE')
      AND (E2.EventDate BETWEEN (E1.EventDate - NumToDSInterval(1,'Minute'))
      AND (E1.EventDate + NumToDSInterval(1,'Minute')));

      SELECT E1.ID, E2.ID
      *
      ERROR at line 1:
      ORA-13268: error obtaining dimension from USER_SDO_GEOM_METADATA
      ORA-06512: at "MDSYS.MD", line 1723
      ORA-06512: at "MDSYS.MDERR", line 8
      ORA-06512: at "MDSYS.SDO_3GL", line 1097
      ---------------------------------------------------------------------------------------------------------------


      Running the query without the EventDate predicate and just the SDO_Within_Distance clause runs without the error, so it's unclear as to where the error message is pointing to.

      Regards,
      Mark