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:
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:
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:
SpatialDB Advisor
Home
Comment