| Problem in import [message #431263] |
Mon, 16 November 2009 06:40  |
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  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #431316 is a reply to message #431263] |
Mon, 16 November 2009 13:38 |
joy_division
Messages: 4963
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
