How to move object from one tablespace to another
DBAsupport.com 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
    Posts
    305
    Hi

    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
    Posts
    13
    Are you using the FROMUSER and TOUSER parameters when doing the import?

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    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
    indexfile=crt.sql

    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 oracle-support@indiatimes.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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
    Posts
    305
    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