Remember quality software? A bit more time upfront saves a ton of trouble later! Agile is cool but not an excuse for slack development.

If you work with large SQL files in Oracle SQL Developer you will get out of memory errors from the Java VM it runs within. To fix this problem, you need to increase the Xmx (maximum heap size) from the default 128MB. If you start SQL Developer by creating a desktop shortcut to SQL Developer.exe, you cannot pass the -Xms parameter and instead need to edit the sqldeveloper\sqldeveloper\bin\sqldeveloper.conf file, adding the following line:-

AddVMOption -Xmx1024M

The file is not formatted with the correct line endings (I guess from Linux/Java build) so you will need to open it with a smart text editor like Visual Studio 2005 or later, which then correctly detects and terminates the lines, otherwise it appears as one big line in Notepad.

About these ads

Comments on: "Configuring Oracle SQL Developer for large files (fix out of memory errors)" (8)

  1. Thanks. It was very helpful.

  2. Innocent JB said:

    Thanx a million !

  3. Hello,
    The error messages ‘Java heap space’ and ‘Protocol violation’ no longer appear after I added the suggested line into the config file. Thank you so much for this useful and helpful tip.

  4. Hi I need to use the developer tool to open a 271MB sql file. I added

    AddVMOption -Xmx1024M

    I still get the same error msg when I tried to open the file.

    • Sorry I can’t help with that. The extended memory settings will help with relatively large (20MB+) files but at some point there will always be a limit (try opening 40MB or more in Windows Notepad). Perhaps you are using a large export file to insert or migrate test or production data? In that case I would look for another standard command line tool from Oracle for bulk export and import. Actually I’m more of an SQL Server specialist, in which case I would be using the BCP utility. Perhaps a search on BCP alternatives for Oracle would help. Good luck!

  5. Alex, you may attempt to up the heap space further. In his example, he uses 1024M (~1GB) but when I needed more to open a file that was >200mb, I up’d this config file line to 2048 and it still worked, like a charm!

  6. Keith Hollins said:

    Just tried this and it worked fine, as for others above.
    But it’s odd that it needs so much memory when all I was doing was opening 3 small files and query an empty table with 50+ VARCHAR2(4000) columns (it was generated by dbms_errlog.create_error_log).
    This is version 4.03 – version 4.01 never gave me a problem, though I may not have queried this table in 4.01.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 37 other followers

%d bloggers like this: