How to move object from one tablespace to another Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: How to move object from one tablespace to another

  1. #1
    Join Date
    Sep 2000

    Can anybody tell me how to move objects from one tablespace to another
    I am giving you the way of action which I am following right now.

    I have one user called user1 and tablspace called tbs1
    when I export the object from user1 and import it to user2 whose default tablespace is tbs2. it is not going to tbs2 it is going into tbs1

    tell me if anybody has knowledge of it.
    Thanks in advance.

  2. #2
    Join Date
    Jan 2002
    Are you using the FROMUSER and TOUSER parameters when doing the import?

  3. #3
    Join Date
    Feb 2001
    There are 2 ways in which you can move objects from one user to another user.

    Method 1:
    If you are using Oracle 8i and above use the follwoing command to move the table from one tablespace to another tablespace:
    sql> alter table emp
    move tablespace TBS2;

    Method 2:

    1) Take the export of the table by the following command
    exp sys file=emp.dmp log=a.log rows=y indexes=y grants=n consistent=y statistics=none

    2) Import the dump file but without the data and index by the following command:

    imp sys file=emp.dmp log=b.log rows=n indexes=n grants=n commit=y ignore=y fromuser=user1 touser=user2

    3) Edit the indexfile and change the tablespace name from tbs1 to tbs2 and run the script.

    4)Again import the dump file with the follwoing command
    imp sys file=emp.dmp log=b.log rows=y indexes=y grants=y commit=y ignore=y fromuser=user1 touser=user2

    In case of any help please be free to ask me at

    Rohit Nirkhe,Oracle DBA,OCP 8i
    Thanks and Regards
    Rohit S Nirkhe

  4. #4
    Join Date
    Apr 2001
    remeber - alter table move tablespace
    will invalidate indexes on that table and any sql in shared pool referencing that object.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Sep 2000
    thanks to all of you I need one more support from you

    rohitsn just tell me what will be the indexfile? What I have to pass in it and what it does?

    thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width