Sunday, December 21, 2014

Opening large files with Oracle SQL Developer

Oracle SQL Developer is my go to SQL tool. It is powerful, easy to use, and free. The only issue is that it can easily produce SQL files that are larger than it can open. For instance if you want to quickly back up a table, you can just run an export in "insert" format and it builds all the insert statements for you. Trouble is if that file's over around 30 MB you can't open it. The issue is that this is a Java program and the space allocated to the JVM at startup isn't large enough for big files. The way to handle this is to increase the memory available to the JVM. Keep in mind when doing this that your OS may limit how much actually gets allocated and of course you could hit a hardware ceiling. So just because you can type a huge number in the configuration file, doesn't necessarily mean you'll get that much memory. Otherwise though this is a simple change that works well.

What you need to do is find the sqldeveloper.conf file. You can just do a search on your system, though keep in mind that some systems (like Mac OS) consider this a system file and don't expose it in the GUI. You may need to use a command line to run your search.

On a Mac you'll likely find the file here: /Applications/SQLDeveloper.app/Contents/Resources/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf

Just open a terminal, use your regular UNIX editor and open the file. Add this line to the file:

AddVMOption  -Xmx2048M

If you find you still can't open your file and that you have more resources available, increase that number until the file opens. Do pay attention to system resources though and leave some memory available for the OS and other apps.