Import and export performace

[jpernia]

Hi guys how are you today.


I need some tips about how improve my imports and export process, I’m running import and exports tables that have 40Million of rows and it are taking to much time.

Do you have any suggestion to me?

Thanks a lot

 

[Turkbear]

Yes, don't do that

Seriously, why such large and repetitive Import/Export
processes?

You can try the DIRECT=Y on the Import to speed things up.

 

[jpernia]

Oh!!! !!! Yes I didn't want but you know is my job, jajajaj

I'm testing a migration process and i just wanted to improve the down-time period,, i going to test the DIRECt=y
i need to read about it because I’ve never used it before,
Thanks and if you have some time to think in anymore that would help me. Thanks.

 

[karluk]

Sorry, but I don't think there is such a thing as direct path import. It's an option only for exports and loads. I made the same mistake a few months ago. See thread186-296467.

It's unfortunate that there is no direct path import, but you should still experment with this option for your exports. It's supposed to speed things up dramatically.

 

[rcurva]

Faster way is to disable all your constraints and drop your indexes of the table (if there are any), then do your import. After the import, enable the contraints and recreate all your indexes.
Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[dklloyd]

Check triggers too on the target database incase inserts are updating other tables that you don't want updated during the import.

dklloyd

 

[oddballz]

Have you given any thought to using transportable tablespaces? This method works very nicely if you are just copying from one instance to another.