Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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
![]() |
![]() |