4 Replies Latest reply: Feb 7, 2014 8:05 AM by R Todd RSS

Select /*csv*/ output truncated with Java Heap Error

R Todd Newbie
Currently Being Moderated

I am using the latest version (4.0.0.13 build MAIN-13.80) on a Win7 64bit machine, with jdk1.7.0_45.

 

I am trying to query a table (and spool to file) using csv comment hint (SELECT /*csv*/ * FROM table).

 

The table has 230 records. Only the first 50 lines appear, then there is an Error in the script output. When I click on the ScriptRunner Task (failed) I see "Java heap space" then more details:

 

java.lang.OutOfMemoryError: Java heap space

    at java.lang.reflect.Array.newArray(Native Method)

    at java.lang.reflect.Array.newInstance(Array.java:70)

etc

 

I've tried changing Preferences...Database...Advanced...SQL Array Fetch size from 50 to 200 (I get 200 lines out).

I've tried changing JVM settings in product.conf to increase memory from 880M to 1250M. No change.

 

Can you suggest anything to try?

  • 1. Re: Select /*csv*/ output truncated with Java Heap Error
    Siva-Oracle Expert
    Currently Being Moderated

    Please try adding -Xmx in sqldeveloper.conf file and check whether it is working:

     

    AddVMOption  -Xmx=512M

     

    Restart SQL Developer after the changes.

  • 2. Re: Select /*csv*/ output truncated with Java Heap Error
    R Todd Newbie
    Currently Being Moderated

    I'm a bit confused, I thought I changed the VM memory options in the product.conf file rather than sqldeveloper.conf.

     

    I did try what you suggested and changed AddVMOption  -Xmx=512M in sqldeveloper.conf.

     

    SQL developer then would not start. Error:

    " Unable to create an instance of the Java Virtual Machine

    Located at path: C:\Program Files\Java\jdk1.7.0_45\jre\bin\server\jvm.dll "

     

    More information about test case using /*csv*/

    1. The table I am querying is very wide. There are 200 columns.

    2. It does seem to work with other tables/queries where there are fewer columns.

    I thought I'd managed to successfully get /*csv*/ to work on an equally wide table so hadnt thought that was the problem. I cannot reproduce it working on this other table any more so perhaps it is the table width?

  • 3. Re: Select /*csv*/ output truncated with Java Heap Error
    Jeff Smith SQLDev PM ACE Moderator
    Currently Being Moderated

    JVM stuff now goes into the product.conf file

     

     

    I think it's the 200 cols. Add a predicate to your query, basically force it to return only 1 or 2 rows and see if it comes back.

  • 4. Re: Select /*csv*/ output truncated with Java Heap Error
    R Todd Newbie
    Currently Being Moderated

    I've forced it to only return 1 or 2 rows as you suggest. The query came back ok; the two records are formatted for csv. They are spooled to a file.

     

     

    I've then reverted it back to the original query (which previously failed) and it now succesfully returns all 233 records in the table, in csv format, spooled to a file.

    No other confiuration settings have changed since the last time i ran it and it failed (i have restarted SQLdeveloper, but i'd also done that many times before and it didnt make any difference those times).

     

    So, sometimes it seems to work, sometimes it doesnt.

Legend

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