How do I move a table from one tablespace to another ?

The Oracle (tm) Users' Co-Operative FAQ

How do I move a table from one tablespace to another ?

For an unpartitioned table, and Oracle 8i, the easiest way is :

	alter table in_wrong_place move tablespace new_tablespace;

If the table is partitioned, then you might need to move all the partitions, or just some of them using the following 8i command :

	alter table in_wrong_place move partition part_wrong tablespace new_tablespace nologging;

The above are the easiest ways - don't forget to rebuild any indexes which you wish to move as well. For versions of Oracle prior to 8i there are a couple of ways to move a table between tablespaces. These are :

Export - import

• export the existing table
• remove quota from the old tablespace
• change the default tablespace to the new tablespace
• drop the old table
• import the table
• change the default tablespace back again
• restore quota on the old tablespace

The following is a worked example of the above :

	as user system ...
	SQL> create user norm identified by norm
	  2  default tablespace tools           
	  3  quota 5m  on tools;                 
	User created.           
	SQL> grant create table, create session to norm;
	Grant succeeded.
	As user norm ...
	SQL> create table test (a number, b varchar2(10);
	Table created.
	SQL> alter table test add constraint test_pk primary key (a);
	Table altered.
	SQL> insert into test values (1,'One');  
	1 row created.                           
	SQL> insert into test values (2, 'Two'); 
	1 row created.                           
	SQL> insert into test values (3, 'Three')
	1 row created.                           
	SQL> commit;
	Commit complete.
	SQL> select * from test;                                                  
	         A B                                                              
	---------- ----------                                                     
	         1 One                                                            
	         2 Two                                                            
	         3 Three                                                          
	SQL> host                                                                    
	$ exp norm/norm file=test.dmp log=test.log statistics=none tables=test compress=no
	Export: Release 8.1.7.0.0 - Production on Mon Jul 9 13:31:53 2001           
	(c) Copyright 2000 Oracle Corporation.  All rights reserved.                
	Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production    
	With the Partitioning option                                                
	JServer Release 8.1.7.0.0 - Production                                      
	Export done in WE8DEC character set and WE8DEC NCHAR character set          
	About to export specified tables via Conventional Path ...                  
	. . exporting table                           TEST          3 rows exported 
	Export terminated successfully without warnings.                            
	$ exit      
	SQL> select table_name ,tablespace_name from user_tables where table_name = 'TEST'
	TABLE_NAME                     TABLESPACE_NAME                                    
	------------------------------ ------------------------------                     
	TEST                           TOOLS
	as user system ...
	SQL> alter user norm default tablespace foliocom quota 0 on tools quota 5m on foliocom;
	User altered.
	as user norm ...
	SQL> drop table test cascade constraints;
	Table dropped.
	SQL> host
	$ imp norm/norm fromuser=norm touser=norm file=test.dmp log=test.log2 ignore=yes commit=yes 
	Import: Release 8.1.7.0.0 - Production on Mon Jul 9 13:44:44 2001                           
	(c) Copyright 2000 Oracle Corporation.  All rights reserved.                                
	Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production                    
	With the Partitioning option                                                                
	JServer Release 8.1.7.0.0 - Production                                                      
	Export file created by EXPORT:V08.01.07 via conventional path                               
	import done in WE8DEC character set and WE8DEC NCHAR character set                          
	. . importing table                         "TEST"          3 rows imported                 
	About to enable constraints...                                                              
	Import terminated successfully without warnings.                                            
	$exit
	SQL> select table_name ,tablespace_name from user_tables where table_name = 'TEST'
	TABLE_NAME                     TABLESPACE_NAME                                    
	------------------------------ ------------------------------                     
	TEST                           FOLIOCOM
	SQL> select * from test;                                                  
	         A B                                                              
	---------- ----------                                                     
	         1 One                                                            
	         2 Two                                                            
	         3 Three                                                          
	as user system ...
	SQL> alter user norm default tablespace tools quota 0 on foliocom quota 5m on tools;
	User altered.

Using temporary tables

• drop constraints from the table
• rename it
• Get some quota on the new tablespace (if not already present !)
• create a new table as select * from old table and put it in the correct tablespace
• add the constraints back to the new table
• drop the old table

The following is a worked example of the above :

	as user norm ...
	SQL> select table_name,constraint_name from user_constraints 
	  2  where table_name = 'TEST';                              
	TABLE_NAME                     CONSTRAINT_NAME               
	------------------------------ ------------------------------
	TEST                           TEST_PK                       
	SQL> select table_name,tablespace_name from user_tables where table_name = 'TEST'
	TABLE_NAME                     TABLESPACE_NAME                                   
	------------------------------ ------------------------------                    
	TEST                           FOLIOCOM                                          
	SQL> alter table test drop constraint test_pk;               
	Table altered.                                               
	SQL> rename test to old_test;
	Table renamed.               
	as user system ...
	SQL> alter user norm quota 5m on dbtables;
	User altered.                             
	as user norm ...
	SQL> create table test tablespace dbtables as (select * from old_test);
	Table created.
	SQL> alter table test add constraint test_pk primary key (a);
	Table altered.	
	SQL> select table_name,tablespace_name from user_tables where table_name = 'TEST'
	TABLE_NAME                     TABLESPACE_NAME                                   
	------------------------------ ------------------------------                    
	TEST                           DBTABLES                                          
	SQL> select * from test;                                                         
	         A B                                                                     
	---------- ----------                                                            
	         1 One                                                                   
	         2 Two                                                                   
	         3 Three                                                                 

The reason for dropping the constraints before renaming the table is that sometimes, Oracle seems to get confused and if the constraints are dropped after renaming the table, it doesn't work and you end up with an orphan constraint.

Further reading: