Home » RDBMS Server » Server Utilities » Problem in import (Solaris10, oracle 10.2.0.3)
Problem in import [message #431263] Mon, 16 November 2009 06:40 Go to next message
harshkumar
Messages: 34
Registered: February 2008
Location: Delhi
Member
Hi,

I am facing the issue in importing the table sized 80GB. The size of total schema is around 400Gb and one table was truncated and importing the data from export backup.

I have disabled all the constraints and dropped all the indexes except one (missed to drop). I used below command to import the data:

imp infodb/infodb tables=actatr indexes=no statistics=none file=exp_actatr.dmp log=imp_actatr.log


The import is running since the last 3.5 days and imported only 41GB of data. I observed that the index that i missed to drop is also growing and currently it grown upto 38GB.

Can i drop / disable that index while the import is running?
Can you suggest any other alternative to make the import process fast.

Thanks in Advance.
Harsh
Re: Problem in import [message #431266 is a reply to message #431263] Mon, 16 November 2009 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63820
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can i drop / disable that index while the import is running?

You will surely get a "BUSY" error.

Quote:
Can you suggest any other alternative to make the import process fast.

Now that it is started, no.
If you restart it, specify a big buffer.

Regards
Michel

[Updated on: Mon, 16 November 2009 07:37]

Report message to a moderator

Re: Problem in import [message #431270 is a reply to message #431263] Mon, 16 November 2009 06:57 Go to previous messageGo to next message
harshkumar
Messages: 34
Registered: February 2008
Location: Delhi
Member
Thanks Michel for your input.
Re: Problem in import [message #431316 is a reply to message #431263] Mon, 16 November 2009 13:38 Go to previous messageGo to next message
joy_division
Messages: 4618
Registered: February 2005
Location: East Coast USA
Senior Member
harshkumar wrote on Mon, 16 November 2009 07:40

Can you suggest any other alternative to make the import process fast.


I think you have already figured out that if that one remaining index was dropped, you'd be golden, but like Michel said, you cannot do that while the import is running.
I have seen import takes up to 8 times as long as an export for a table.
Think about what Oracle is doing...it is building the index as the table is being INSERTed for the entire 80Gb. That's A LOT of I/O.
Re: Problem in import [message #431337 is a reply to message #431316] Mon, 16 November 2009 23:01 Go to previous messageGo to next message
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
Junior Member
Just to add, if you are using Oracle 10g or higher, please try impdp over imp - it is faster given the same size and volume.....

The reason being, exp/imp are clients. Communication between the database instance and the exp utility that writes the export dump file (or from the imp utility to the database instance) goes through SQLNet whereas expdp/impdp are server processes. These processes attach to the SGA and are hence quicker.
Re: Problem in import [message #431548 is a reply to message #431263] Wed, 18 November 2009 01:11 Go to previous message
harshkumar
Messages: 34
Registered: February 2008
Location: Delhi
Member
Thanks a lot to all for your inputs.

I have the oracle spatial data so i though may be some features will not be available using data pump so i took the export using exp.

I now stopped the already running imp process and restarted the import process again after dropping the index. Now it is uploading around 1 crore records per min. After finishing the import, I will create the index again.

Thanks for your support.
Harsh
Previous Topic: SQL*Loader and NULL value
Next Topic: Mulitple tables data loading
Goto Forum:
  


Current Time: Sat Oct 01 16:12:35 CDT 2016

Total time taken to generate the page: 0.07196 seconds