Re: EXP/IMP to a different tablespace: has anyone been able to solve this?

Oracle version I'm using is 10g r2+

Using DBA-role accounts results in the same. It uses the default tablespace for empty tables, but goes against the original tablespace for the tables with more than 1 row.
With the "quota unlimited" option for all tablespaces or on the source tablespace, it will create all objects in the original tablespace, ignoring the "touser"'s default tablespace.

The good news is that data pumps will work instead (if you're lucky to be on a version that supports these).

The process in such case boils down to the following:

expdp ${src_schema} dumpfile=${src_schema}.dmp directory=dmpdir schemas=${src_schema}

impdp ${dest_schema} dumpfile=${src_schema}.dmp directory=dmpdir \  remap_schema="(${src_schema}:${dest_schema})" \  remap_tablespace="(${src_tablespace1}:${dest_tablespace1},$ {srtc_tablespace2}:${dest_tablespace2})"

sqlplus ${dest_schema} <<!
begin

   DBMS_UTILITY.COMPILE_SCHEMA(upper('${dest_schema}')); end;
/
!

It's still slow as mould, but at least it eliminates the need to use IMP/EXP. Received on Wed Oct 24 2007 - 14:52:28 CDT