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.

<<generate_series: an Oracle implementation in light of SQL Design Patterns >>How to restart a database after failed parameter change

Name		Remember
E-mail
http://
Message
	Textile Help