8 Replies Latest reply: Feb 28, 2014 3:37 PM by Gary Graham-Oracle RSS

    Cannot open large files

    Mark Reichman

      SQL Developer 4.0 and 4.1 have this issue.

       

      I cannot open sql files (text)  180MB and 558Mb in size.  I have other files < 127MB that open fine.  All of these SQL files were generated by SQL Developer and contain DDL and insert statements.

       

      I adjusted the  c:\user\<user>\AppData\Roaming\sqldeveloper\1.0.0.0.0\product.conf file.

       

      AddVMOption -Xms1024m

      AddVMOption -Xmx1024m

       

      SQL Developer still wont open the files.  Whats up?

        • 1. Re: Cannot open large files
          Jeff Smith Sqldev Pm-Oracle

          What happens when you try?

           

          Try bumping up the memory to 2048

          • 2. Re: Cannot open large files
            tvCa-Oracle

            I'm thinking your files are too big. At some point, you gotta give in ... 10 or 20 megs, OK, but 600 ?

            • 3. Re: Cannot open large files
              Jeff Smith Sqldev Pm-Oracle

              When we open a file, we're not JUST loading it into memory - we're also parsing it. Not cheap on very large files.

               

              If you want to run it, reference it via the @ the command.

              • 4. Re: Cannot open large files
                Mark Reichman

                I forgot to add the files open fine in Notepad and Notepad++...  Maybe they aren't parsing..

                2048 mem settings have same issue.

                 

                I was going to recommend to the user to just run the file at the sql prompt with the @ symbol. However, the insert statements have a different schema prefix and need to be edited before running.  So, I'll edit the file(s) in notepad++ and then run the files in sqlplus or sql developer using the @ symbol.  Seems weird though that I have to go to another editor to do this.

                 

                Thanks..

                • 5. Re: Cannot open large files
                  Jeff Smith Sqldev Pm-Oracle

                  Not only are we parsing, but we're also tracking changes to the file. A lot more going on than what notepad++ is doing.

                   

                  How are you generating the files? Might be possible to have SQLDev have them go out with the desired SCHEMA prefixes for those INSERTS so no editing is even required.

                  • 6. Re: Cannot open large files
                    Mark Reichman

                    I guess I could click off "show schema" in the DDL section of the SQL Developer export.  That's OK though.. Thanks for the help.  I have work a around .  Here is the error I get just in case you can make sense of this and the issue is something else.

                     

                    java.io.IOException: exception loading C:\ESRI\refmkr_milept_xref.sql

                        at oracle.ide.model.TextNode.openImpl(TextNode.java:549)

                        at oracle.ide.model.Node.open(Node.java:1045)

                        at oracle.ide.model.Node.open(Node.java:992)

                        at oracle.ide.model.Node.ensureOpen(Node.java:2417)

                        at oracle.ide.model.TextNode.acquireTextBuffer(TextNode.java:767)

                        at oracle.dbtools.worksheet.editor.WorksheetGUI.<init>(WorksheetGUI.java:818)

                        at oracle.dbtools.worksheet.editor.Worksheet.createGUI(Worksheet.java:1027)

                        at oracle.dbtools.worksheet.editor.Worksheet.getWorksheetGUI(Worksheet.java:1375)

                        at oracle.dbtools.worksheet.editor.Worksheet.getGUI(Worksheet.java:1190)

                        at oracle.ide.editor.Editor.getDefaultFocusComponent(Editor.java:233)

                        at oracle.dbtools.worksheet.editor.Worksheet.getDefaultFocusComponent(Worksheet.java:1151)

                        at oracle.dbtools.worksheet.editor.Worksheet.open(Worksheet.java:1472)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.EditorState.openEditor(EditorState.java:318)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.SplitPane.doLayout(SplitPane.java:580)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.whenCurrentEditorChanges(NbEditorManager.java:1591)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.whenCurrentEditorChanges(TabGroup.java:1026)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.setCurrentTabGroupState(TabGroup.java:847)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.addTabGroupState(TabGroup.java:129)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.createEditor(NbEditorManager.java:546)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.createEditor(NbEditorManager.java:523)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.openEditor(NbEditorManager.java:391)

                        at oracle.ide.cmd.OpenCommand.openWithNoProject(OpenCommand.java:337)

                        at oracle.ide.cmd.OpenCommand.access$100(OpenCommand.java:62)

                        at oracle.ide.cmd.OpenCommand$1.run(OpenCommand.java:266)

                        at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:251)

                        at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:733)

                        at java.awt.EventQueue.access$200(EventQueue.java:103)

                        at java.awt.EventQueue$3.run(EventQueue.java:694)

                        at java.awt.EventQueue$3.run(EventQueue.java:692)

                        at java.security.AccessController.doPrivileged(Native Method)

                        at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)

                        at java.awt.EventQueue.dispatchEvent(EventQueue.java:703)

                        at oracle.javatools.internal.ui.EventQueueWrapper._dispatchEvent(EventQueueWrapper.java:169)

                        at oracle.javatools.internal.ui.EventQueueWrapper.dispatchEvent(EventQueueWrapper.java:151)

                        at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:242)

                        at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:161)

                        at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:150)

                        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:146)

                        at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:138)

                        at java.awt.EventDispatchThread.run(EventDispatchThread.java:91)

                    Caused by: java.lang.OutOfMemoryError: Java heap space

                        at oracle.javatools.buffer.VectorBuffers.consolidateBuffers(VectorBuffers.java:131)

                        at oracle.javatools.buffer.VectorBuffers.getAllBuffers(VectorBuffers.java:156)

                        at oracle.javatools.buffer.EOLNormalizer.processFinish(EOLNormalizer.java:190)

                        at oracle.javatools.buffer.EOLNormalizer$ReaderNormalizer.normalizeData(EOLNormalizer.java:312)

                        at oracle.javatools.buffer.AbstractTextBuffer.read(AbstractTextBuffer.java:1406)

                        at oracle.javatools.buffer.AbstractTextBuffer.read(AbstractTextBuffer.java:1350)

                        at oracle.javatools.buffer.TextBufferDecorator.read(TextBufferDecorator.java:468)

                        at oracle.ide.model.TextNode.loadTextBuffer(TextNode.java:307)

                        at oracle.ide.model.TextNode.openImpl(TextNode.java:537)

                        at oracle.ide.model.Node.open(Node.java:1045)

                        at oracle.ide.model.Node.open(Node.java:992)

                        at oracle.ide.model.Node.ensureOpen(Node.java:2417)

                        at oracle.ide.model.TextNode.acquireTextBuffer(TextNode.java:767)

                        at oracle.dbtools.worksheet.editor.WorksheetGUI.<init>(WorksheetGUI.java:818)

                        at oracle.dbtools.worksheet.editor.Worksheet.createGUI(Worksheet.java:1027)

                        at oracle.dbtools.worksheet.editor.Worksheet.getWorksheetGUI(Worksheet.java:1375)

                        at oracle.dbtools.worksheet.editor.Worksheet.getGUI(Worksheet.java:1190)

                        at oracle.ide.editor.Editor.getDefaultFocusComponent(Editor.java:233)

                        at oracle.dbtools.worksheet.editor.Worksheet.getDefaultFocusComponent(Worksheet.java:1151)

                        at oracle.dbtools.worksheet.editor.Worksheet.open(Worksheet.java:1472)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.EditorState.openEditor(EditorState.java:318)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.SplitPane.doLayout(SplitPane.java:580)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.whenCurrentEditorChanges(NbEditorManager.java:1591)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.whenCurrentEditorChanges(TabGroup.java:1026)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.setCurrentTabGroupState(TabGroup.java:847)

                        at com.oracle.jdeveloper.nbwindowsystem.editor.TabGroup.addTabGroupState(TabGroup.java:129)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.createEditor(NbEditorManager.java:546)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.createEditor(NbEditorManager.java:523)

                        at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.openEditor(NbEditorManager.java:391)

                        at oracle.ide.cmd.OpenCommand.openWithNoProject(OpenCommand.java:337)

                        at oracle.ide.cmd.OpenCommand.access$100(OpenCommand.java:62)

                        at oracle.ide.cmd.OpenCommand$1.run(OpenCommand.java:266)

                    • 7. Re: Cannot open large files
                      rp0428

                      I was going to recommend to the user to just run the file at the sql prompt with the @ symbol. However, the insert statements have a different schema prefix and need to be edited before running.

                      In the future you may want to just not include the schema prefix in the files.

                       

                      Then you can just use the appropriate schema when the file is executed.

                       

                      A control script can execute an ALTER SESSION to switch schemas before each script.

                      ALTER SESSION SET CURRENT_SCHEMA = SCOTT;
                      -- execute a script of 'scott' objects
                      ALTER SESSION SET CURRENT_SCHEMA = HR;
                      - execute a script of 'hr' objects
                      • 8. Re: Cannot open large files
                        Gary Graham-Oracle

                        Hi Mark,

                         

                        Reading through the stack trace you provided, I see ...

                        Caused by: java.lang.OutOfMemoryError: Java heap space

                        I agree entirely with user793618 ...

                        I'm thinking your files are too big. At some point, you gotta give in ... 10 or 20 megs, OK, but 600 ?

                        In the immortal words of Stan Lee, "'Nuff said".