This discussion is archived
5 Replies Latest reply: Mar 21, 2012 8:28 PM by 923417 RSS

Export to Excel Hangs

user6830576 Newbie
Currently Being Moderated
It seems that under certain circumstances (presumably the number/size of the records set), the Export (of query results) to Excel hangs. In my case, the Export to xls slows down at about 9,000 records. If I open TaskManager, I see both sqldeveloper.exe and perl.exe (combined) consuming close to 100% CPU. Additionally, it doesn't seems that canceling the Export is possible (e.g. CPU remains pegged and you can't close the SQL Developer Window, becuase the Connection remains in use/locked). To "escape", I need to kill the sqldeveloper.exe and perl.exe processes via Task Manager. Is this a known issue? As a workaround, I found that I am able to export the same data set to a text file without any problems.
  • 1. Re: Export to Excel Hangs
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    This is a known limitation since at least the SQL Developer 2.1 release according to these two references:

    Bug 7190115 - CANNOT EXPORT LARGE TABLE TO XLS FILE
    Bug 7499213 - SQL DEVELOPER HANGS WHEN EXPORTING LARGE AMOUNT OF DATA

    If you click on the Help button of the Export Wizard and scroll down a bit in the Help Center page, you will see a note something like this:

    For exporting large tables to Microsoft Excel files:
    - If you encounter problems, try adding the following line to the sqldeveloper.conf file to increase heap size and then restarting SQL Developer: AddVMOption -Xmx1024M
    - If the number of table rows exceeds 65,536, SQL Developer writes the rows to multiple worksheets within the .xls file

    But as an end-user, it's usually a losing battle trying to fight these virtual memory limit cases. Exporting to the csv file format also works fine, and Excel operates on those perfectly well. Just open it in Excel, then re-save it in xls format if you prefer.

    Regards,
    Gary Graham
    SQL Developer Team
  • 2. Re: Export to Excel Hangs
    924664 Newbie
    Currently Being Moderated
    I'm getting these lock ups on exporting to CSV too in the latest version. Well...in the previous version also, but now i'm on 3.1. Any ideas? Windows 7 64-bit, running the 32-bit version.
  • 3. Re: Export to Excel Hangs
    rp0428 Guru
    Currently Being Moderated
    >
    I'm getting these lock ups on exporting to CSV too
    >
    Increase the heap size like Gary suggested, restart sqldeveloper and try it again.
  • 4. Re: Export to Excel Hangs
    Gary Graham Expert
    Currently Being Moderated
    Hi,

    Some more ideas if you are wish to pursue additional Java VM tuning:

    1. Use the Garbage-First Garbage Collector:
    Re: Memory problems with Oracle sql developer

    and/or

    2. Some general tuning tips for JDeveloper which also apply to SQL Developer. Update either jdev.conf or sqldeveloper.conf:
    https://blogs.oracle.com/angelo/entry/improving_the_performance_of_jdeveloper

    Be sure not to use the garbage collection optimization from (2) - UseConcMarkSweepGC - if you decide to try (1).

    Regards,
    Gary
    SQL Developer Team
  • 5. Re: Export to Excel Hangs
    923417 Newbie
    Currently Being Moderated
    For most of data exporting issue, the article below can solve them:
    http://www.codeproject.com/KB/cs/Excel_PDF_Word_ExportWiz.aspx?msg=3768323#xx3768323xx

    For developers, i'd like to share a Free Data Export Component:
    http://www.e-iceblue.com/Introduce/free-dataexport-component.html

    Edited by: 920414 on 2012-3-21 下午8:28

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points