I finally managed to do this and I share it here for the benefit of the list. I also like to thank all the members taking time responding to my questions. Here is what I did: User 1: Exports Data from the schema he owns, his default tablespace is called tblspc1 User 2: Imports Data to his own schema, his default tablespace is called tblspc2 $ sqlplus / as sysdba > grant create any directory to user1; > grant create any directory to user2; > exit $ sqlplus user1/pass > create or replace directory dump_dir as '/u02/dmp' > exit $ expdp user1/pass1 schemas=user1 directory=dump_dir dumpfile=expfile.dmp logfile=explog.log $ sqlplus user2/pass2 > create or replace directory dump_dir as '/u02/dmp' > exit $ impdp user1/pass1 directory=dump_dir dumpfile=expfile.dmp logfile=implog.log REMAP_SCHEMA=user1:user2 REMAP_TABLESPACE=tblspc1:tblspc2 to be sure, I recompiled all the procedures, functions and triggers. That went well. Thanks everyone. > Amir On Wed, Nov 26, 2008 at 2:58 PM, Teijo Lallukka <teijo.lallukka@xxxxxxxx>wrote: > Hi! > > Maybe I haven't followed whole discussion from this topic but I wan't to > suggest... > > If you are using 10g... why not using Data pump? > > 1) expdp original data... > > 2) impdp with REMAP_SCHEMA & REMAP_TABLESPACE options to different user > (copy) > > 3) If data needs to get to original user then use alter table... move > command to copied data and rename objects if needed. > > is this usable? > > -Teijo L. > Oracle DBA > > >>> "Amir Gheibi" <gheibia@xxxxxxxxx> 26.11.2008 4:20 >>> > Thanks for the reply. My objective is to copy (not move) all the data > (including tables) from one tablespace to another. I don't think Exchange > Partition does it for me. > > On Wed, Nov 26, 2008 at 1:15 AM, Dennis Williams < > oracledba.williams@xxxxxxxxx> wrote: > > > Amir, > > > > Are any of your tables really large? I saw a demonstration years ago > where > > EXCHANGE PARTITION was used to change the ownership of a table. > > Basically you can use EXCHANGE PARTITION to switch the ownership. You > first > > exchange the table into a partition, then exchange that partition to > another > > table owned by another user. Since this is just a dictionary change, > there > > is no execution delay. I haven't needed to try this myself, but haven't > > heard this mentioned in awhile. > > > > Dennis Williams > > > >