Oracle Spatial index creation fails with ORA-29855 if OLS is enabled at schema Level (Doc ID 1093674.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 10.2.0.3 and later
Information in this document applies to any platform.
Checked for relevance on 24-Dec-2013

Symptoms

It is not possible to create successfully an R-Tree Spatial index on tables with mdsys.sdo_geometries columns if these tables belong to schemas with OLS policies  enabled.

An OLS policy is enabled at schema level in this way :

execute sa_policy_admin.apply_schema_policy('<MY_POLICY_NAME>','MY_SCHEMA_NAME>');

With OLS enabled at the schema level  the creation of  R-Tree spaitial index on the mdsys.geometries
columns ends with 
ORA-29855 error occured during index creation errors

which is caused by ORA-00947 error 'not enough values'.

the following example shows the error faced:


conn / as sysdba

-- set up the schema user that will be used for this example

drop user my_schema cascade;
create user my_schema identified by my_schema;
grant connect, resource to my_schema;

-- example setup

conn my_schema/my_schema

create table table_location(a varchar2(200),
location SDO_GEOMETRY)
partition by range(a)
(partition p1 values less than(maxvalue));

connect lbacsys/lbacsys

-- create OLS policy

begin
SA_SYSDBA.CREATE_POLICY(
policy_name => 'OLS_POLICY',
column_name => 'SECLAB',
default_options => 'READ_CONTROL,WRITE_CONTROL,INVERSE_GROUP');
end;
/

-- enable the policy at schema level
execute sa_policy_admin.apply_schema_policy('OLS_POLICY','My_Schema');

conn my_schem/my_schema
-- insert sdo metadata

insert into user_sdo_geom_metadata values
('TABLE_LOCATION',
'LOCATION',
sdo_dim_array(sdo_dim_element('X',-180,180,1),
sdo_dim_element('Y',-90,90,1)),8307);
commit;

-- show the error when creating the R-Tree spatial index:

create index table_location_idx
on table_location(location)
indextype is mdsys.spatial_index local;
create index table_location_idx
*
ERROR at line 1:
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-29400: data cartridge error
ORA-00947: not enough values
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 1

-- alternative occurrence of the error when trying to rebuild the index
--  error from ODCIINDEXALTER routine

conn my_schema/my_schema

drop index table_location_idx;

create index table_location_idx
on table_location(location)
indextype is mdsys.spatial_index local UNUSABLE;

Index created.

alter index table_location_idx rebuild partition p1;

alter index table_location_idx
*
ERROR at line 1:
ORA-29858: error occurred in the execution of ODCIINDEXALTER routine
ORA-29400: data cartridge error
ORA-00947: not enough values
ORA-13249: internal error in Spatial index: [mdidxrbd]
ORA-13249: Error in Spatial index: index build failed
ORA-29400: data cartridge error
ORA-00947: not enough values
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 313

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