Home » RDBMS Server » Server Utilities » import to different tablespace (oracle 10g windows xp)
import to different tablespace [message #514159] Sat, 02 July 2011 00:01 Go to next message
mrdb
Messages: 62
Registered: December 2009
Location: dubai
Member
How to import a dump file into different tablespace.

normally when i am importing a dump file it goes to the table space where it was exported but i need to import in to different tablespace

Report message to a moderator

Re: import to different tablespace [message #514160 is a reply to message #514159] Sat, 02 July 2011 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 26759
Registered: January 2009
Location: SoCal
Senior Member
>How to import a dump file into different tablespace.
>normally when i am importing a dump file it goes to the table space where it was exported but i need to import in to different tablespace

if you pre-create objects, then old tablespace do not matter!

Report message to a moderator

Re: import to different tablespace [message #514166 is a reply to message #514160] Sat, 02 July 2011 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67753
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another way is to give 0 quota on the old tablespace and set user default tablespace as the now one.

Regards
Michel

Report message to a moderator

Re: import to different tablespace [message #514264 is a reply to message #514159] Sun, 03 July 2011 04:58 Go to previous messageGo to next message
hvdtol84
Messages: 1
Registered: July 2011
Junior Member
If you do the export / import with DataPump you can use the REMAP_TABLESPACE commamd during the import.
REMAP_TABLESPACE=tnsfroma:tbstob.

Regards,

hvdtol84

Report message to a moderator

Re: import to different tablespace [message #514268 is a reply to message #514264] Sun, 03 July 2011 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67753
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe he should but when you receive a dump file you don't choose which way the sender export is done.

Regards
Michel

Report message to a moderator

Re: import to different tablespace [message #514696 is a reply to message #514268] Tue, 05 July 2011 23:51 Go to previous messageGo to next message
dkdms2124
Messages: 369
Registered: April 2010
Location: INDIA
Senior Member
Hello Sir,

My question just to clear my doubt, if we are not aware that which using which utility the export was done [datapump or normal import/export], which should be the default approach one should try while doing import.

And one try using simple import utility for the dump file which was created(export) using datapump utility, will that be imported successfully?

Regards
Deepak

Report message to a moderator

Re: import to different tablespace [message #514697 is a reply to message #514696] Tue, 05 July 2011 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 26759
Registered: January 2009
Location: SoCal
Senior Member
>And one try using simple import utility for the dump file which was created(export) using datapump utility, will that be imported successfully?
NO!

Report message to a moderator

Re: import to different tablespace [message #514706 is a reply to message #514697] Wed, 06 July 2011 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67753
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My question just to clear my doubt, if we are not aware that which using which utility the export was done [datapump or normal import/export], which should be the default approach one should try while doing import.

Any one, if you don't choose the correct tool, the import will fail. You have 1/2 chance.

Regards
Michel

[Updated on: Wed, 06 July 2011 00:50]

Report message to a moderator

Re: import to different tablespace [message #515680 is a reply to message #514159] Tue, 12 July 2011 20:46 Go to previous messageGo to next message
rsager
Messages: 17
Registered: June 2011
Location: Sydney, Australia
Junior Member
Hi mrdb...

I assume you are exporting from the source DB and importing into a target DB...One way is...
1. Find the schema owner(s) that are defined for the source tablespace (tblsp) from the dba_users view as sys
2. Recreate these users into the target DB and point them at the new tblsp
3. In the imp script, cammand, or bat file, user the FROMUSER and TOUSER param for the imp utility and them ran a import. All the objects exported should now import into the new tblsp.
Cheers
Roger

Report message to a moderator

Re: import to different tablespace [message #515736 is a reply to message #515680] Wed, 13 July 2011 02:41 Go to previous message
Michel Cadot
Messages: 67753
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what have been said a week ago?

In addition, "from the dba_users view as sys", NO, never use SYS for this kind of things. SYS is for database physical maintenance (and afew of other things) NOT for data maintenance.

Regards
Michel

[Updated on: Wed, 13 July 2011 02:44]

Report message to a moderator

Previous Topic: POST_TABLE_ACTION in IMPDP
Next Topic: Dump from 9i DB to be imported to 11g directly
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Mar 03 05:36:41 CST 2021
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.