Parallel direct load on BLOB column using SQL Loader
I have a requirement wherein I need to load tonnes of data in table using SQL Loader. This is done via a regular shell script.. My requirement is that I need to fire up multiple SQL Loader sessions with direct load set to true. Since all SQL Loader session try to insert data in on single table, I get the resource busy error message. If I try to set PARALLEL to true along with Direct = TRUE, I get the following error:
SQL*Loader-971: parallel load option not allowed when loading lob columns
I do have a BLOB column (storage out of row) in this table. Is there any feasible solution to overcome this restriction? I need to insert data at a very high rate and thought that the SQL Loader direct load will be a good way to do that. But it seems that SQL Loader cannot do a Direct parallel load on BLOB column.. Why is this restriction there?
Here is a wild and untested idea to workaround "resource busy" issue; partition your table and point each SQL Loader stream to a different partition.
As per the Why is this restriction there? question, that's like the Why the chicken crossed the road? one; probably nobody knows and it is actually inmaterial, you just have to deal with your chicken being compeled to cross the road
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Thanks for your suggestion. Will try it out and post the results, if interesting.
but i dont think u can still use direct method.. how about external table?
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Click Here to Expand Forum to Full Width