Changing the tablespace of a table 4

[uparna]

Hi ,
Is it possible to change the tablespace that a table is stored in ? In other words : if my table "T" is in a tablespace "A" already and i want to change the table "T" to another tablespace "B" , would this be possible ? I need to do this without bringing the database down.....what really is cause for my concern is this : what about the constraints (PK,FK etc) that the table has now?
Please respond at the earliest.
My sinciere thanks to all. Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[THoey]

Uparna,

I don't know about other versions, but with 8, you can issue an ALTER TABLE command. I think it is:

ALTER TABLE TableName MOVE NewTablespace;

It should move everything including the constraints. Hope that helps... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[uparna]

Hi Terry ,
i really cant tell you how thankful i am to your reply. Kudos pal , you deserve a treat !
|-0|-0|-0|-0|-0|-0|-0|-0|-0*:->*|-0|-0|-0|-0|-0|-0|-0|-0|-0

Thanks again !
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .I would be glad to help you out.

 

[jeswanthj]

the command to move table space is
alter table <tablename> move tablespace <tablespace name>

 

[raygg]

Can the same be done for indexes?

 

[uparna]

Ryagg ,
for the indexes , there is the rebuild command which will do the same : for eg :

ALTER INDEX index_name REBUILD TABLESPACE index_tablespace ;

Please note that any major changes in the table (like moving tables across tablespaces etc) will necessitate the rebuilding the indexes.

It is always better to rebuild the indexes on tables that have large changes , regularly .
Regards,
S. Jayaram Uparna .

If the need arises,you are welcome to mail me at oracguru@yahoo.com .