5 Replies Latest reply: Mar 21, 2012 10:28 PM by 923417 RSS

    Export to Excel Hangs

    user6830576
      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-Oracle
          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
            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
              >
              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-Oracle
                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
                  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