Fixing failed spatial indexes after import using data pump

Data pump is a great utility, more flexible and compatible than the old imp/exp utility. One area where it is useful is in the remapping of tablespaces. I often get data from customers in data pump format in which references to tablespaces that I do not have in my Oracle database exist. But this is relatively easy to fix via the remap_tablespace option as the following example command shows:

 C:\> impdp ***/****@gisdb directory=data_pump_dir dumpfile=GIS_20070709_160457.DMP ^
 More? remap_tablespace=SPATIALDATA:USERS ^
 More? remap_tablespace=SPATIALINDEX:USERS ^
 More? LOGFILE=GIS_20070709_160457.log

Notice how I can have multiple remap_tablespace parameters.

Now this works fine to a point. All objects, such as tables and ordinary indexes, have TABLESPACE keywords in the DDL that is exported by data pump as follows:

 CREATE TABLE ABS_MESH_BLOCK (
   FID NUMBER(11),
   HEIGHT FLOAT(126),
   ROTATION FLOAT(126),
   TEXTSTRING VARCHAR2(254),
   MB_CODE NUMBER(13, 0),
   MB_TYPE CHAR(1),
   MB_CATEGORY CHAR(50),
   MOD_DATE DATE,
   GEOM MDSYS.SDO_GEOMETRY
 )
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 STORAGE(INITIAL 4194304 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
 TABLESPACE SPATIALDATA LOGGING NOCOMPRESS
 
 CREATE INDEX ABS_MESH_BLOCK_FID
           ON ABS_MESH_BLOCK (FID)
           TABLESPACE SPATIALINDEX;

These objects are happily remapped by data pump.

However, what data pump does not do, when importing, is apply the remap_tablespace to the TABLESPACE= element within the PARAMETERS clause of an Oracle spatial index:

 CREATE INDEX ABS_MESH_BLOCK_GEOM
           ON ABS_MESH_BLOCK (GEOM )
           INDEXTYPE IS MDSYS.SPATIAL_INDEX
           PARAMETERS ('SDO_INDX_DIMS=2 TABLESPACE=SPATIALINDEX LAYER_GTYPE=MULTIPOLYGON');

I guess this is because the TABLESPACE information is within a _text string_.

After import, if the actual tablespace referenced in the PARAMETERS clause does not exist in the target database, the spatial index will exist but have a FAILED status. To fix this one can write a trivial bit of PL/SQL which will change the name referenced by the TABLESPACE= element and then rebuild the index.

The following PL/SQL should be saved in a SQL text file (my version is called _index_rebuild.sql_).

 DEFINE FROM_TSPACE=&1
 DEFINE TO_TSPACE=&2
 set serveroutput on size unlimited verify off
 declare
   v_diminfo mdsys.sdo_dim_array := MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',325000,498600,0.01),MDSYS.SDO_DIM_ELEMENT('Y',6370000,6550000,0.01));
   v_srid    number := 82469;
   v_sql     varchar2(4000);
 
   cursor c_indexes is
   select uic.table_name, uic.index_name, uic.column_name, ui.parameters
     from user_indexes ui,
          user_ind_columns uic
    where ui.index_type      = 'DOMAIN'
      and ui.domidx_opstatus = 'FAILED'
      and UPPER(ui.parameters) like '%TABLESPACE=%'
      and INSTR(UPPER(ui.parameters),UPPER('TABLESPACE=&&FROM_TSPACE.')) > 0
      and uic.table_name = ui.table_name
      and exists (select 1
                    from all_sdo_index_metadata sim
                   where sim.sdo_index_name = ui.index_name);
 
 begin
  for rec in c_indexes loop
    begin
       delete from user_sdo_geom_metadata where table_name = rec.table_name and column_name = rec.column_name;
       exception
         when no_data_found then
            null;
    end;
    insert into user_sdo_geom_metadata values(rec.table_name,rec.column_name,v_diminfo,v_srid);
    commit;
    begin
      v_sql := 'alter index ' || rec.index_name || ' rebuild PARAMETERS(''' || REPLACE(rec.parameters,'&&FROM_TSPACE.','&&TO_TSPACE.') ||''')';       dbms_output.put(v_sql || ' - ');
      execute immediate v_sql;
      dbms_output.put_line('SUCCESSFUL');
      exception
       when others then
          dbms_output.put_line('FAILED (' || SUBSTR(SQLERRM,1,240) || ')');
    end;
  end loop;
 end;
 /
 quit;

This SQL script can be called, from a Windows command shell, as follows:

 c:\> sqlplus ***/****@gisdb @index_rebuild SPATIALINDEX USERS

I hope this is helpful to someone.