Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Moving tables to different tablespace

ShazimJan 13 2011 — edited Jan 14 2011
Hi
In our database for some users the tablespaces are wrongly assigned. We want to move those users tables to different tablespace. The tables do not contain any indexes or constrains. How can I perform this.

Comments

sb92075
ALTER TABLE <YOUR_TABLE> MOVE ......
672680
1) Change your user`s default tablespace to the one you want assign him to: 
alter user your-user-nme default tablespace YOUR-TBS-NME;
2) use the output of this sql to move the tables to designated tablespace: 
select 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE YOUR-TBS-NME;' from USER_TABLES;
Shazim
After moving the tables using the following command "alter table move tablespace "do i need to any other changes. Please let me know.
672680
If you have indexes, then need to rebuild. other than that, NO.
Shazim
HOW ABT CONSTRAINTS.
Umesh Gupta
Hi Iman,

First of all identify the tables which u want to move..
select owner,table_name,tablespace_name from dba_tables where owner = 'HR'
Then move the tables u wanted to move
alter table table_name move tablespace new_tablespace_name;
after moving the table u have to explicitly move normal index, LOB-indexes
are moved implicitly, but normal index remains in older
tablespace

so u have to issue fiollowing command to move index to new tablespace
alter index index_name rebuild tablespace new_tablespace_name;
I think constarints are automatically moved to new tablespace with table, u dnt need to do anything.

and u should also change the default tablespace for those schmea's as well
so that from next time all objects will be created in defult schema..

Regards

Umi
CKPT
Imankhan wrote:
After moving the tables using the following command "alter table move tablespace "do i need to any other changes. Please let me know.
If any Dependent indexes available on the moved table, Those becomes invalid so you need to rebuild them also.
wiZ
Why don't you use dbms_redefinition? It copies all constraints, indexes and so..

It's more complex, but it doesnt lock the table (okay, just for a short moment at the end...)

see: http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN01514

Regards

Edited by: user11175580 on 14.01.2011 00:27
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 11 2011
Added on Jan 13 2011
#general-database-discussions
8 comments
54,313 views