CODEWITHSUNDEEP
oracle-databaseindexingspatial
Bishwajit Purkaystha
Bishwajit Purkaystha

Reputation: 2185

Failed to successfully execute the ODCIIndexCreate routine. - Oracle Spatial Indexing

I have two oracle spatial tables, namely, restaurants and persons. The structures of these two tables are:

CREATE TABLE restaurants(
 id NUMBER PRIMARY KEY,
 name VARCHAR2(32),
 shape SDO_GEOMETRY
);

CREATE TABLE persons(
 p_id NUMBER PRIMARY KEY,
 p_name VARCHAR2(32),
 p_shape SDO_GEOMETRY
);

Then, after populating each table I wanted to create spatial index for each table. The following instruction for restaurants table executed successfully.

CREATE INDEX restaurants_spatial_idx
ON restaurants(shape)
INDEXTYPE IS mdsys.spatial_index;

But, when I wrote for persons table:

CREATE INDEX persons_spatial_idx
ON persons(p_shape)
INDEXTYPE IS mdsys.spatial_index;

This gave me the following error in sql developer 3.2.20:

Error starting at line 340 in command:

CREATE INDEX persons_spatial_idx

ON persons(p_shape)

INDEXTYPE IS mdsys.spatial_index

Error at Command Line:340 Column:14

Error report: SQL Error: ORA-00955: name is already used by an existing object 00955. 00000 - "name is already used by an existing object"

Cause:
Action:

As this was saying that I have already and index named persons_spatial_idx, I deleted this index using: DROP INDEX persons_spatial_idx; Then when I tried to create the index again, it produced the following error:

Error starting at line 340 in command:

CREATE INDEX persons_spatial_idx ON persons(p_shape) INDEXTYPE IS mdsys.spatial_index

Error at Command Line:340 Column:14

Error report:

SQL Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

ORA-13249: internal error in Spatial index: [mdidxrbd]

ORA-13249: Error in Spatial index: index build failed

ORA-13249: Error in spatial index: [mdrcrtxfergm]

ORA-13249: Error in spatial index: [mdpridxtxfergm]

ORA-13200: internal error [ROWID:AAAFGnAABAAALHpAAA] in spatial indexing.

ORA-13206: internal error [] while creating the spatial index

ORA-13365: layer SRID does not match geometry SRID

ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 10

  1. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"

*Cause: Failed to successfully execute the ODCIIndexCreate routine.

*Action: Check to see if the routine has been coded correctly.

Thanks for reading.

Upvotes: 0

Views: 9930

Answers (2)

Markuss Siksna
Markuss Siksna

Reputation: 1

For any one who has encountered this type of error in Oracle SQL I sorta found a work around by modifying the meta-data of GEOM_METADATA. Changing the SRID to NULL in the table ALL_SDO_GEOM_METADATA for the table where the geometry is located then the index is created successfully.

! This is probably not a good solution and I do not know the full implications of changing the SRID to NULL !

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

The message is saying that an object with name PERSONS_SPATIAL_IDX already exists. You can use the following query to find the existing index:

select *
from all_indexes
where index_name = 'PERSONS_SPATIAL_IDX'

Upvotes: 0

Related Questions