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.
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.
SQL Developer Team
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
2. Some general tuning tips for JDeveloper which also apply to SQL Developer. Update either jdev.conf or sqldeveloper.conf:
Be sure not to use the garbage collection optimization from (2) - UseConcMarkSweepGC - if you decide to try (1).
SQL Developer Team
For most of data exporting issue, the article below can solve them:
For developers, i'd like to share a Free Data Export Component:
Edited by: 920414 on 2012-3-21 下午8:28