REBUILD ONLINE On Spatial Index Hangs With "qerex_gdml" (Doc ID 1907207.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Spatial and Graph - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

While trying to rebuild a functional index online the alter index hangs

alter index cities_func_sidx rebuild online parameters('TABLESPACE="USERS"');

Here is a simplified test case

conn / as sysdba

-- Tests as a user
-- Clean up from any previous running of this procedure.
DROP USER spatial_test CASCADE;
--Create user
create user spatial_test identified by spatial_test
default tablespace users temporary tablespace temp;

-- Grant privileges --
GRANT connect, resource to spatial_test;
GRANT create table, create sequence to spatial_test;

CONNECT spatial_test/spatial_test

set echo on
set serveroutput on
spool tc.log

CREATE TABLE cities_xy(
city varchar2(20),
city_longitude number,
city_latitude number);

create or replace function get_geom(
longitude in number,
latitude in number)
return sdo_geometry
deterministic is
begin
  return sdo_geometry (2001, 8307,
  sdo_point_type(longitude, latitude, NULL), NULL, NULL);
end;
/

delete user_sdo_geom_metadata where table_name='CITIES_XY';

INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'CITIES_XY',
'SPATIAL_TEST.GET_GEOM(CITY_LONGITUDE,CITY_LATITUDE)',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .5),
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .5)
),
8307
);

drop index cities_func_sidx;
create index cities_func_sidx on
cities_xy(spatial_test.get_geom(city_longitude, city_latitude))
indextype is mdsys.spatial_index;

-- This hangs

alter index cities_func_sidx rebuild online parameters('TABLESPACE="USERS"');

spool off

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