Re: How to move tables between tablespaces ?

In article <2artrg$re2_at_seismo.CSS.GOV>, cooper_at_seismo.CSS.GOV (Dale Cooper) writes:
|>In article 1993Oct26.060617.23912_at_amoco.com, zdxc0d_at_amoco.com (David Crowson)
|>writes:
|> In article g4k_at_netserver.univ-lille1.fr, noel_at_omega.univ-lille1.fr
|> (Yves Noel) writes:
|> -->
|> -->Hi all DBAs Oracle !
|> -->Is there an easy way to move tables from a tablespace to another one ? I've
|> -->an idea but I'm not sure it's the best.
|> -->Thanks in advance.
|> -->--
|>>The way I would do it is this:
|>>
|>>1) create an export of the table
|>>2) import usinf indexfile=Y to create an import create script
|>>3) edit the created file and change the tablespace name to whatever you want
|>>4) run the file in sqlplus
|>>5) import the data
|>
|>
|>================================================================================
|>In article 2ami3e$h0p_at_gaia.ucs.orst.edu, mickel_at_OES.ORST.EDU (Paul Mickel)
|>writes:
|>[David Crowson's solution deleted]
|>
|>>I suppose that another idea may be to do the following:
|>>
|>>0. create the table in the tablespace you want to final place to be
|>>1. do an insert into that table, adding a 'select' containing the
|>> data from the old table, i.e.,
|>>
|>> SQL_LIVE> insert into extract.newtable values
|>> 2> as select <fields from oldtable in the order of new one>
|>> 3> from oldtablespace.tablename
|>> 4> where <where condition if necessary>
|>>2. delete the data from the old table AFTER you're sure that it works.
|>>3. drop the old table
|>>
|>>I've done this on two tables in the same tablespace, so it should be extend-
|>>able. I don't have the manual in front of me to check the syntax of the
|>>insert statement, so check that before you start. I also don't know if you
|>>can use the 'rename' command and use the tablespace.table format to do this.
|>>
|>>Just some thoughts.
|>>
|>>-Paul
|>
|>
|>================================================================================
|>In article CFMCE2.2tz_at_cyf-kr.edu.pl, ufstycze_at_cyf-kr.edu.pl (Barbara Styczen )
|>(Rafal actually) writes:
|>>
|>>It's simple just use export and import !
|>>
|>>Rafal Styczen
|>
|>
|>================================================================================
|>And last but not least...
|>
|>In article MLOENNRO.93Oct27163520_at_wrpyr4.us.oracle.com, mloennro_at_us.oracle.com
|>(Magnus Lonnroth) writes:
|>
|>>In article <2ami3e$h0p_at_gaia.ucs.orst.edu> mickel_at_OES.ORST.EDU (Paul Mickel)
|>>writes:
|>-> In article <1993Oct26.060617.23912_at_amoco.com>,
|>-> David Crowson <zdxc0d_at_amoco.com> wrote:
|>-> >In article g4k_at_netserver.univ-lille1.fr, noel_at_omega.univ-lille1.fr
|>-> (Yves Noel) writes:
|>-> >-->
|>-> >-->Hi all DBAs Oracle !
|>-> >-->Is there an easy way to move tables from a tablespace to another one ?
|>-> >-->I've
|>-> >-->an idea but I'm not sure it's the best.
|>-> >-->Thanks in advance.
|>-> >-->--
|>-> >The way I would do it is this:
|>-> >
|>-> >1) create an export of the table
|>-> >2) import usinf indexfile=Y to create an import create script
|>-> >3) edit the created file and change the tablespace name to whatever you
|>-> > want
|>-> >4) run the file in sqlplus
|>-> >5) import the data
|>
|>-> I suppose that another idea may be to do the following:
|>->
|>-> 0. create the table in the tablespace you want to final place to be
|>-> 1. do an insert into that table, adding a 'select' containing the
|>-> data from the old table, i.e.,
|>->
|>-> SQL_LIVE> insert into extract.newtable values
|>-> 2> as select <fields from oldtable in the order of new one>
|>-> 3> from oldtablespace.tablename
|>-> 4> where <where condition if necessary>
|>
|>This will not work with LONG columns. Use copy instead. I hope
|>"oldtablespace" is a typo. Should be table-owner or just excluded.
|>
|>-> 2. delete the data from the old table AFTER you're sure that it works.
|>
|>No, don't do that. Just drop the table.
|>
|>-> 3. drop the old table
|>
|>Now all you have to do is manually recreate all indexes, constraints, and
|>grants.
|>
|>-> I've done this on two tables in the same tablespace, so it should be
|>-> extendable. I don't have the manual in front of me to check the syntax
|>-> of the insert statement, so check that before you start. I also don't
|>-> know if you can use the 'rename' command and use the tablespace.table
|>-> format to do this.
|>->
|>-> Just some thoughts.
|>
|>>A much easier method is to take advantage of the way import (re)creates
|>>tables: if it exists, it's used, and constraints, grants, and indexes may
|>>optionally be added after importing rows. If the table doesn't exist, an
|>>attempt is made to create it in the same tablespace (as exported). If the
|>>user doesn't have privileges for this, an attempt is made to create it in
|>>the user's default tablespace. This is really usefull for moving a large
|>>number of tables belonging to one user between tablespaces. Heres an
|>>example of moving all of scott's tables from system to users:
|>>
|>>exp scott/tiger file=scott.dmp constraints=y
|>>sql> revoke resource from scott;
|>>sql> grant resource on users to scott;
|>>sql> alter user scott default tablespace users;
|>>[drop scott's objects now]
|>>imp scott/tiger file=scott.dmp commit=y grants=y
|>>
|>>This will recreate everything (grants, indexes, constraints). You have an
|>>option of creating indexes in another tablespace by using the indexfile
|>>parameter during import. This can also be done more selectively by specifying
|>>table-names as parameters to exp, imp, or both.
|>>
|>>cheers,
|>>
|>>Magnus Lonnroth
|>>Oracle Sweden
|>>Mail: mloennro_at_oracle.com
|>================================================================================
|>
|>Now MY turn!
|>
|>I suppose one more suggestion is appropriate:
|>
|>How about:
|>
|> SQL> create table table_in_new_space
|> 2 tablespace new_tablespace
|> 3 storage (...)
|> 4 as select * from table_in_old_space;
|>
|> (you won't find this one in the manual!)
|>
|> SQL> (recreate appropriate indexes and re-issue grants and constraints)
|>
|> SQL> drop table table_in_old_space;
|>
|> SQL> rename table_in_new_space to table_in_old_space;
|>
|>
|>There are a number of assumptions here.
|>
|> 1) The user has resource privilege on the new tablespace.
|> 2) The user knows which indexes and constraints exist and
|> how to recreate them.
|>
|> One can use the exp indexfile option to do all of that fun
|> stuff for you.
|>
|>Just goes to show, there's a ton of ways to do the same thing. Pick whichever
|>is most appropriate.
|>
|>Cheers!
|>
|>
|>Dale Cooper, DBA "Ask not what your country can do for you,
|>Center for Seismic Studies ask what you can do for your country" -- JFK
|>Arlington, VA
|> How soon we forget...*sigh*
|>
IN FACT THE EASIEST AND FINEST SOLUTION IS IN ORACLE BOOK : Utilities User's Guide (version 6.0, page 3-3, chapter Reorganizing Tablespaces). With this solution you are sure to recover all grants, constraints, ...etc which belong to the tables.

-- 
------------------------------------------------------------------------------
   _/_/_/_/    _/_/_/_/   _/_/_/_/   _/_/_/_/                  I use
  _/_/          _/_/       _/_/       _/_/               ORACLE v6.0.36.5.2
 _/_/          _/_/       _/_/       _/_/                       on        
_/_/_/_/ .  _/_/_/_/ .   _/_/ .   _/_/_/_/ .            DEC RISC ULTRIX v4.3
------------------------------------------------------------------------------
Yves NOEL   -   Database Administrator
C.I.T.I. (batiment M4)                             Phone : (33)  20.43.42.70
Universite des Sciences & Technologies de Lille    Fax   : (33)  20.43.66.25 
59655 Villeneuve d'Ascq Cedex - FRANCE             Email : noel_at_univ-lille1.fr
------------------------------------------------------------------------------