| 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 ? | |
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 :
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.
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: