Re: How to move a user's tables from one tablespace to another tablespace.

Poon Chak Yau (cy_poon_at_ctil.com) wrote:

: Hello,
: 	I want to move a user's tables from SYSTEM table space to
: another table space. Can anyone tell me how to do it  without
: affecting operations?
: 
: Regards,

Poon Chak,

There are several methods to do this. You can either:

1. export the table, drop the table, create the table definition in the new tablespace, and then import the data (imp ignore=y).
2. Create a new table in the new tablespace with the "CREATE TABLE x AS SELECT * from y" command:

   CREATE TABLE temp_name TABLESPACE new_tablespace AS SELECT * FROM real_table;

   Then drop the original table and rename the temporary table as the original:

   DROP TABLE real_table;
   RENAME temp_name TO real_table;

After #1 or #2 is done, be sure to recompile any procedures that may have been invalidated by dropping the table. I prefer method #1, but #2 is easier if there are no indexes, constraints, or triggers. If there are, you must manually recreate them.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

<-> For 80+ Oracle tips, visit my Web Page:                       <->
<->                                                               <->
<->              http://homepage.interaccess.com/~akaplan         <->
<->                                                               <->
<->             email: akaplan_at_interaccess.com                    <->