ORA-20171 When Trying to Drop the Spatial Index on a Version Enabled Table (Doc ID 1236545.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Workspace Manager - Version 10.2.0.1 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 05-Jun-2013***

Symptoms

You have a table with geometry column 


CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape MDSYS.SDO_GEOMETRY);
-- table created.

desc COLA_MARKETS

Name                   Null?    Type
---------------------- -------- ----------------------------
MKT_ID                 NOT NULL NUMBER
NAME                            VARCHAR2(32)
SHAPE                           MDSYS.SDO_GEOMETRY


You have created a spatial index on it : 

-- metadata needed to be able to create the spatial index

INSERT INTO USER_SDO_GEOM_METADATA
VALUES ('cola_markets','shape',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('X', 0, 20, 0.005),
MDSYS.SDO_DIM_ELEMENT('Y', 0, 20, 0.005)),NULL);
-- 1 row created.

commit;
-- Commit complete.

CREATE INDEX cola_spatial_idx ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
-- Index created.


This table has been version enabled:

exec dbms_wm.enableversioning('COLA_MARKETS');
-- PL/SQL procedure successfully completed.


Now trying to drop spatial index but it fails with following error :

exec dbms_wm.beginddl('COLA_MARKETS');
-- PL/SQL procedure successfully completed.

drop index COLA_SPATIAL_IDX;
drop index COLA_SPATIAL_IDX
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20171: WM error: Indexes on versioned objects cannot be dropped.
ORA-06512: at "WMSYS.WM_ERROR", line 342
ORA-06512: at "WMSYS.WM_ERROR", line 359
ORA-06512: at "WMSYS.NO_VM_DROP_PROC", line 207
ORA-06512: at line 21


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms