Re: tables

From: Oracleguru, Suresh Bhat <oracleguru_at_mailcity.com>
Date: Tue, 16 Mar 1999 17:38:54 GMT
Message-ID: <01be6fe3$4222f880$a504fa80@mndnet>

Hi Naveen -

Yes you can, but involves little work. I had done this while ago so make sure that parameters are correct as you go through these steps.

First export the specific table(s) 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 given below. 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.

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

show = y
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 or by copying them to something else.

---

---

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

naveen_arora_at_my-dejanews.com wrote in article <7clt2i$8jr$1_at_nnrp1.dejanews.com>...

> hi,
> i initially created tables from a user account with tablespace A
> i now have changed the default tablespace of user to B
> is there any way to move the extends and data from tablespace A to B ?
> thanks for any help
> naveen_at_cdotd.ernet.in
> 
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own  
 

> Received on Tue Mar 16 1999 - 11:38:54 CST