OraFAQ Forum: Server Utilities » Indexes and tables import to different tablespaces.

Home » RDBMS Server » Server Utilities » Indexes and tables import to different tablespaces. (Oracle 10G R2, OS windows)

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

Indexes and tables import to different tablespaces. [message #439708]

Tue, 19 January 2010 03:28

JuicyFruit
Messages: 34
Registered: September 2009

Member

Hello!

Here is the question:

we have a full export dump of DB (using exp utl.) Is it possible to import that database using exp or expdp in such way, that all the tables will be in e.g. Tablespace1 and all indexes in Tablespace2? Export was done when Tables and Indexes all were in Tablespace0.

Report message to a moderator

Re: Indexes and tables import to different tablespaces. [message #439709 is a reply to message #439708]

Tue, 19 January 2010 03:33

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Hi

here is the answer

sriram Smile

Report message to a moderator

Re: Indexes and tables import to different tablespaces. [message #439712 is a reply to message #439709]

Tue, 19 January 2010 03:44

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

in addition
Quote:

dump files generated by the original export utility (exp) cannot be imported by the new data pump import utility (impdp) and vice-versa.

Quote:

Is it possible to import that database using exp or expdp in such way

exp Or Expdp or exporting tool which used to take the logical backup
Imp and impdp are the import tools.

i suggest you to read ...
Wiki.oracle

Oracle base data pump

sriram Smile

Report message to a moderator

Re: Indexes and tables import to different tablespaces. [message #439847 is a reply to message #439709]

Tue, 19 January 2010 12:36

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

If you precreate the indexes (of course you would have to precreate the tables first), then as the import runs, it will be rebuilding the index on the fly for every row, which cane make the import at least 10 times longer than if done the traditional way where the indexes are build at the end.

I would just do an

ALTER TABLE MOVE TABLESPACE

and

ALTER INDEX REBUILD

command.

Is there a legitimate reason that you need to do this? Separating indexes from tables is an ancient custom that is no longer necessary.

Report message to a moderator

Previous Topic:	Import Schema
Next Topic:	Exp/Imp of 1 TB database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 05:09:04 CDT 2024