Home » SQL & PL/SQL » SQL & PL/SQL » bulk export (Windows XP, ORacle 10g)
bulk export [message #320742] Fri, 16 May 2008 03:07 Go to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
Hi,

I have a requirement to export data in a large table with 50-60 columns with 2000000 records. I need to export this data to flat file, i cannot use UTL package since the users cannot be given privileges for creating directory and creating utl files.

Any one have any idea whether this can be done using DBMS_FILE package. The idea is to bulk spool the data from the table.

Kindly help how we can use dbms_file package for the same.


Thanks and Regards,

NN
Re: bulk export [message #320753 is a reply to message #320742] Fri, 16 May 2008 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
spool myfile
select * from mytable;
spool off

Regards
Michel

Re: bulk export [message #320763 is a reply to message #320753] Fri, 16 May 2008 03:34 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I cannot use normal spool to do this as it may take atleast 2 days to the spooling completed. As i said for the reason earlier, i cannot use UTL file package as well. I would prefer using a bulk collect method and write to text file using DBMS_FILE package , but i have used this very long ago and don't have any manuals handy for the syntax of DBMS_FILE. If any one have the dbms_file sample code , please do share it , it will be helpful.

Thanks
NN
Re: bulk export [message #320765 is a reply to message #320742] Fri, 16 May 2008 03:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is DBMS_FILE? There's not reference to it in the 9i, 10g or 11g packages manuals, or on Metalink.

Are you thinking about UTL_FILE?

Is this a one off export, or must it be done repeatedly?
Re: bulk export [message #320770 is a reply to message #320763] Fri, 16 May 2008 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set termout off
set arraysize 100
spool myfile
select * from mytable;
spool off

This is the fastest way.

Regards
Michel
Re: bulk export [message #320772 is a reply to message #320770] Fri, 16 May 2008 03:52 Go to previous messageGo to next message
nirmalnarayan
Messages: 261
Registered: April 2005
Location: India
Senior Member
I have already tried spooling. this took me two days to spool 2000000 records of table.
That's why i am looking for any solution with DBMS_FILE.

This procedure will be run from client side, Is it possible to write the file to client machine directory (Using DBMS_FILE package). Is the same achievable by using UTL_FILE package.

Thanks
NN.
Re: bulk export [message #320783 is a reply to message #320772] Fri, 16 May 2008 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have already tried spooling. this took me two days to spool 2000000 records of table.

This has nothing to do with spooling, you will not have better performances with anything.
Note that this query MUST be executed on server to prevent from network exchanges maybe this is why you had bad peformances.

Regards
Michel
Re: bulk export [message #320809 is a reply to message #320772] Fri, 16 May 2008 05:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Unless it's something you've written, there is no DBMS_FILE package included with the Oracle database.

Re: bulk export [message #320810 is a reply to message #320772] Fri, 16 May 2008 05:25 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have a read through this link. I am using the pro*c version for my extract. On an average every month we extract around 80M and it is the fastest method which I can think of exporting such huge volume. Job finishes approx hour and half.

http://asktom.oracle.com/tkyte/flat/index.html

Regards

Raj

P.S : I missed to mention we run this extract on the server side and not the client side.

[Updated on: Fri, 16 May 2008 05:31]

Report message to a moderator

Re: bulk export [message #320851 is a reply to message #320772] Fri, 16 May 2008 07:58 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Just to add to Michel's post, you will need to execute the code from a file with a START or @ command or else you will still get the output to the screen.
Previous Topic: Single Index with many columns or Multiple Indexes with fewer columns?
Next Topic: data from 3 tables and last row (merged)
Goto Forum:
  


Current Time: Fri Dec 09 21:22:39 CST 2016

Total time taken to generate the page: 0.05122 seconds