Home » RDBMS Server » Server Utilities » Import from one tablespace to another
Import from one tablespace to another [message #441929] Wed, 03 February 2010 23:32 Go to next message
babar82
Messages: 108
Registered: March 2009
Location: Karachi
Senior Member
Hi,

I want to import data from a user "Payroll" in Oracle9i database to another user with same name in Oracle10g. The oracle9i user is in system tablespace but I have placed the 10g user in another tablespace "ts_payroll". Now I want all the tables to be imported in new tablespace (ts_payroll). What do I need to do because when I run import utility, it imports all the tables in system tablespace?
Re: Import from one tablespace to another [message #441933 is a reply to message #441929] Wed, 03 February 2010 23:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
* Set the default tablespace of your user to the one you want and do not grant it quota on system tablespace (do not grant it DBA or RESOURCE or UNLIMITED TABLESPACE)

* Other solution, precreate the objects in the tablespace you want and then import the data.

Regards
Michel
Re: Import from one tablespace to another [message #441934 is a reply to message #441929] Wed, 03 February 2010 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>What do I need to do because when I run import utility, it imports all the tables in system tablespace?
Before running import you need to CREATE the empty tables (& indexes?) in the new tablespace; then use IGNORE=YES with imp
Re: Import from one tablespace to another [message #443061 is a reply to message #441934] Fri, 12 February 2010 05:58 Go to previous messageGo to next message
explorer8
Messages: 3
Registered: February 2010
Junior Member
Is there any way to generate "CREATE TABLE/INDEXES" scripts for all objects in a schema?

Re: Import from one tablespace to another [message #443062 is a reply to message #443061] Fri, 12 February 2010 06:10 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_METADATA package or Data Pump.

Regards
Michel

[Updated on: Fri, 12 February 2010 06:11]

Report message to a moderator

Previous Topic: Cloning the DB
Next Topic: Hiding password in exp command
Goto Forum:
  


Current Time: Mon Sep 26 12:49:44 CDT 2016

Total time taken to generate the page: 0.12708 seconds