Re: Vs: Re: Transfer Data to another tablespace with different name

  • From: "Amir Gheibi" <gheibia@xxxxxxxxx>
  • To: "Teijo Lallukka" <teijo.lallukka@xxxxxxxx>
  • Date: Thu, 27 Nov 2008 18:31:50 +0800

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
> >
>
>

Other related posts: