How to move object from one tablespace to another

[shailendra]

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.

[xsf3190]

Are you using the FROMUSER and TOUSER parameters when doing the import?

[rohitsn]

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

[Sureshy]

remeber - alter table move tablespace
will invalidate indexes on that table and any sql in shared pool referencing that object.

[shailendra]

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