Go to content Go to navigation and search

Home

Current Oracle Spatial Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Fixing failed spatial indexes after import using data pump

Saturday November 15 2008 at 09:40

Keywordsdata pump spatail failed index

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.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment