OraFAQ Forum: Server Utilities » Import from one tablespace to another

Home » RDBMS Server » Server Utilities » Import from one tablespace to another

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import from one tablespace to another [message #441929]

Wed, 03 February 2010 23:32

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?

Report message to a moderator

Re: Import from one tablespace to another [message #441933 is a reply to message #441929]

Wed, 03 February 2010 23:37

Michel Cadot
Messages: 68645
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

Report message to a moderator

Re: Import from one tablespace to another [message #441934 is a reply to message #441929]

Wed, 03 February 2010 23:38

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Report message to a moderator