The Oracle (tm) Users' Co-Operative FAQ

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


Author's name: Norman Dunbar;

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 09/07/2001

Oracle version(s): 7.3.4 to 8.1.7

I've created a table in the wrong tablespace and I want to move it into the correct one. How do I go about doing this ?

Back to index of questions


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

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

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:


Back to top

Back to index of questions