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

expdp original data...

impdp with REMAP_SCHEMA & REMAP_TABLESPACE options to different user (copy)

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" 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_at_gmail.com> 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
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Amir Gheibi at Nov 27, 2008 at 10:31 am
    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 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" 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_at_gmail.com> 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
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedNov 26, '08 at 6:58a
activeNov 27, '08 at 10:31a
posts2
users2
websiteoracle.com

2 users in discussion

Amir Gheibi: 1 post Teijo Lallukka: 1 post

People

Translate

site design / logo © 2022 Grokbase