Re: Moving table to another tablespace

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Tue, 28 Jul 1998 19:55:16 GMT
Message-ID: <01bdba70$fa6a0ec0$a504fa80@mndnet>

First export the specific tables to a dump file such as suresh.dmp under dba account such as ops$dba.

exp parfile=export_tables_parfile.txt

The content of this parfile, export_tables_parfile.txt is:

userid  = /
file    =/r1/export/suresh.dmp
rows    =n
log     =$HOME/log/export.log
tables  =(
	   table1,
	   table2
	 )
____________________________________________________________________________

___

Then import the tables. This import will ceate only the script file, import_script.sql. THE STRUCTURES AND DATA WILL NOT BE IMPORTED. Trust me, I have checked it out personally.

imp parfile=import_tables_parfile1.txt

Contents of the import_tables_parfile1.txt is given below. The file, import_script.sql will contain the SQL script for tables (table1, table2 in this case), their indexes, constraints, grants, triggers etc. with appropriate storage parameters. Alter this script to suit your needs and use it to create the tables/indexes/grants/constraints/triggers or any of the above in the appropriate tablespace for any user with your own storage parameters.

userid  = /
file    =/r1/export/suresh.dmp
log     =$HOME/log/import_script.sql
tables  =(
	   table1,
	   table2	
            )


----------------------------------------------------------------------------


----------------------------------------------

Drop the tables. Before dropping production tables, you may want to make sure that your export file,suresh.dmp is good by importing few tables in some account, ops$suresh, as given below.

---

---

Use the above altered script to create the tables in appropriate tablespace and/or user.

---

---

Then do the real import with the following script. You may want to add other import options to this script.
Find out about these option by entering: imp help = y.

imp parfile=import_tables_parfile2.txt

Contents of the import_tables_parfile2.txt is given below.

userid  = /
commit  =y
file    =/r1/export/suresh.dmp
fromuser=ops$dba
touser	= ops$suresh
ignore  =y
log     =$HOME/log/importing_in_new_tablespace.log
tables  =(
	   table1,
	   table2	
            )

That should do it !!!

suresh.bhat_at_mitchell-energy.com

TurkBear <johng_at_mm.com> wrote in article <35bcaaa3.10192696_at_news2.mm.com>...

> Hi,
> What is the best way to move a table ( or several tables) from one
> tablespace to another? I would like to avoid the obvious - creating
> the table structure in the new tablespace with a different name, and
> using 'insert into....' statements to populate it with the data from
> the original table, dropping the original and renaming the new one....
> 
> Any other way?
> 
> Thanks,
> John Greco
> System : Oracle 7.3, Netware 4.11
> 
> Reply here or email to : john.greco_at_dot.state.mn.us
> 
> Thanks....
> 

Received on Tue Jul 28 1998 - 14:55:16 CDT