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

Poon Chak Yau (cy_poon@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:                       <->
<->                                                               <->
<->              www.arikaplan.com                                <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page htm">Back to Ari Kaplan's Home Page