Re: Move table on an other tablespace

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Tue, 01 Sep 1998 15:34:52 GMT
Message-ID: <01bdd5cd$b50bbfe0$a504fa80@mndnet>

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

exp parfile=export_tables_parfile.txt

The content of this parfile, export_tables_parfile.txt is: To import all the tables, delete tables=(...,...)

userid  = /
file    =/r1/export/xyz.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/xyz.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,xyz.dmp is good by importing few tables in some account, ops$abc, 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/xyz.dmp
fromuser=ops$dba
touser	= ops$abc
ignore  =y
log     =$HOME/log/importing_in_new_tablespace.log
tables  =(
	   table1,
	   table2	
            )

Good luck !!!

Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

Imaginet <mparmentier_at_sqli.fr> wrote in article <6sgqta$4j7$1_at_news.imaginet.fr>...

> With Oracle 7.3, I must move all my table on an other tablespace.
> How i can made this action ?
> 
> regards
> mparmentier_at_sqli.fr
> 
> 
> 
> 

Received on Tue Sep 01 1998 - 10:34:52 CDT