Import data by oracle sql developer 18.2

3756369

    I've got error importing 1.000.000 of rows (csv type).

     

    I am currently testing Oracle exadata express and i am currently working with Oracle sql developer app to upload the data.

    After loading a schema (migration from mysql) I would like to import data to the oracle database. To do that purpose I have all the table data in csv format and i use oracle sql developer to connect to the database.

    When i try to import the data (1Milion rows) of a table it shows me an error and it only inserts 11183 rows.

    I am using linux and the error is displayed into the terminal where i have launched the sql developer app:

     

    java.io.IOException: exception loading /tmp/Import-stats-csv-bad_4.sql
             at oracle.ide.model.TextNode.openImpl(TextNode.java:544)
             at oracle.ide.model.Node.open(Node.java:1252)
             at oracle.ide.model.Node.open(Node.java:1199)
             at oracle.ide.model.Node.ensureOpen(Node.java:2650)
             at oracle.ide.model.TextNode.acquireTextBuffer(TextNode.java:762)
             at oracle.dbtools.worksheet.editor.WorksheetGUI.<init>(WorksheetGUI.java:834)
             at oracle.dbtools.worksheet.editor.Worksheet.createGUI(Worksheet.java:1099)
             at oracle.dbtools.worksheet.editor.Worksheet.getWorksheetGUI(Worksheet.java:1473)
             at oracle.dbtools.worksheet.editor.Worksheet.getGUI(Worksheet.java:1272)
             at oracle.ide.editor.Editor.getDefaultFocusComponent(Editor.java:233)
             at oracle.dbtools.worksheet.editor.Worksheet.getDefaultFocusComponent(Worksheet.java:1233)
             at oracle.dbtools.worksheet.editor.Worksheet.open(Worksheet.java:1574)
             at com.oracle.jdeveloper.nbwindowsystem.editor.EditorState.openEditor(EditorState.java:318)
             at com.oracle.jdeveloper.nbwindowsystem.editor.SplitPane.doLayout(SplitPane.java:579)
             at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.whenCurrentEditorChanges(NbEditorManager.java:1637)
             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:556)
             at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.createEditor(NbEditorManager.java:533)
             at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.openEditor(NbEditorManager.java:401)
             at oracle.ide.editor.EditorUtil.openDefaultEditorInFrame(EditorUtil.java:181)
             at oracle.dbtools.raptor.data.writers.ImportGen$1$1.run(ImportGen.java:522)
             at oracle.ide.util.IdeUtil$3.run(IdeUtil.java:1089)
             at oracle.bali.ewt.util.PaintUtils.invokeAfterRepaint(PaintUtils.java:53)
             at oracle.ide.util.IdeUtil.invokeAfterRepaint(IdeUtil.java:1102)
             at oracle.dbtools.raptor.data.writers.ImportGen$1.run(ImportGen.java:520)
             at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
             at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
             at java.awt.EventQueue.access$500(EventQueue.java:97)
             at java.awt.EventQueue$3.run(EventQueue.java:709)
             at java.awt.EventQueue$3.run(EventQueue.java:703)
             at java.security.AccessController.doPrivileged(Native Method)
             at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
             at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
             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:205)
             at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
             at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:109)
             at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:190)
             at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:235)
             at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:233)
             at java.security.AccessController.doPrivileged(Native Method)
             at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:233)
             at java.awt.Dialog.show(Dialog.java:1084)
             at java.awt.Component.show(Component.java:1671)
             at java.awt.Component.setVisible(Component.java:1623)
             at java.awt.Window.setVisible(Window.java:1014)
             at java.awt.Dialog.setVisible(Dialog.java:1005)
             at oracle.dbtools.raptor.backgroundTask.dialogs.TaskDialog.showDialogNow(TaskDialog.java:200)
             at oracle.dbtools.raptor.backgroundTask.dialogs.TaskDialog.access$100(TaskDialog.java:32)
             at oracle.dbtools.raptor.backgroundTask.dialogs.TaskDialog$DialogShower.run(TaskDialog.java:174)
             at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:311)
             at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:758)
             at java.awt.EventQueue.access$500(EventQueue.java:97)
             at java.awt.EventQueue$3.run(EventQueue.java:709)
             at java.awt.EventQueue$3.run(EventQueue.java:703)
             at java.security.AccessController.doPrivileged(Native Method)
             at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)
             at java.awt.EventQueue.dispatchEvent(EventQueue.java:728)
             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:205)
             at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
             at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
             at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
             at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
             at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
    Caused by: java.lang.OutOfMemoryError: Java heap space
             at oracle.javatools.buffer.VectorBuffers.addBuffer(VectorBuffers.java:96)
             at oracle.javatools.buffer.EOLNormalizer.process(EOLNormalizer.java:157)
             at oracle.javatools.buffer.EOLNormalizer$ReaderNormalizer.normalizeData(EOLNormalizer.java:307)
             at oracle.javatools.buffer.AbstractTextBuffer.read(AbstractTextBuffer.java:1403)
             at oracle.javatools.buffer.AbstractTextBuffer.read(AbstractTextBuffer.java:1347)
             at oracle.javatools.buffer.TextBufferDecorator.read(TextBufferDecorator.java:468)
             at oracle.ide.model.TextNode.loadTextBuffer(TextNode.java:302)
             at oracle.ide.model.TextNode.openImpl(TextNode.java:532)
             at oracle.ide.model.Node.open(Node.java:1252)
             at oracle.ide.model.Node.open(Node.java:1199)
             at oracle.ide.model.Node.ensureOpen(Node.java:2650)
             at oracle.ide.model.TextNode.acquireTextBuffer(TextNode.java:762)
             at oracle.dbtools.worksheet.editor.WorksheetGUI.<init>(WorksheetGUI.java:834)
             at oracle.dbtools.worksheet.editor.Worksheet.createGUI(Worksheet.java:1099)
             at oracle.dbtools.worksheet.editor.Worksheet.getWorksheetGUI(Worksheet.java:1473)
             at oracle.dbtools.worksheet.editor.Worksheet.getGUI(Worksheet.java:1272)
             at oracle.ide.editor.Editor.getDefaultFocusComponent(Editor.java:233)
             at oracle.dbtools.worksheet.editor.Worksheet.getDefaultFocusComponent(Worksheet.java:1233)
             at oracle.dbtools.worksheet.editor.Worksheet.open(Worksheet.java:1574)
             at com.oracle.jdeveloper.nbwindowsystem.editor.EditorState.openEditor(EditorState.java:318)
             at com.oracle.jdeveloper.nbwindowsystem.editor.SplitPane.doLayout(SplitPane.java:579)
             at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.whenCurrentEditorChanges(NbEditorManager.java:1637)
             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:556)
             at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.createEditor(NbEditorManager.java:533)
             at com.oracle.jdeveloper.nbwindowsystem.NbEditorManager.openEditor(NbEditorManager.java:401)
             at oracle.ide.editor.EditorUtil.openDefaultEditorInFrame(EditorUtil.java:181)
             at oracle.dbtools.raptor.data.writers.ImportGen$1$1.run(ImportGen.java:522)
             at oracle.ide.util.IdeUtil$3.run(IdeUtil.java:1089)
             at oracle.bali.ewt.util.PaintUtils.invokeAfterRepaint(PaintUtils.java:53)
    java.lang.NullPointerException
             at oracle.dbtools.raptor.insight.InsightableOracleDatabase.fetch(InsightableOracleDatabase.java:704)
             at oracle.dbtools.parser.plsql.TabCol.fetchDbCols(TabCol.java:577)
             at oracle.dbtools.parser.plsql.TabCol.fetchColumns(TabCol.java:460)
             at oracle.dbtools.parser.plsql.TabCol.fetchColumns(TabCol.java:396)
             at oracle.dbtools.raptor.phighlight.StarAdvice.refresh(StarAdvice.java:98)
             at oracle.dbtools.raptor.phighlight.StarAdvice.starAdvice(StarAdvice.java:80)
             at oracle.dbtools.raptor.phighlight.SemanticAnalysis.doIt(SemanticAnalysis.java:178)
             at oracle.dbtools.raptor.phighlight.HighlightEditorPlugin.stateChanged(HighlightEditorPlugin.java:442)
             at oracle.dbtools.raptor.plsql.BackgroundParser.parse(BackgroundParser.java:284)
             at oracle.dbtools.raptor.plsql.BackgroundParser$1.construct(BackgroundParser.java:172)
             at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
             at java.lang.Thread.run(Thread.java:748)
    java.sql.SQLRecoverableException: Closed Connection
             at oracle.jdbc.driver.PhysicalConnection.getMetaData(PhysicalConnection.java:2408)
             at oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCConnection$2oracle$1jdbc$1internal$1OracleConnection$$$Proxy.getMetaData(Unknown Source)
             at oracle.dbtools.raptor.insight.InsightableOracleDatabase.fetch(InsightableOracleDatabase.java:691)
             at oracle.dbtools.parser.plsql.TabCol.fetchDbCols(TabCol.java:577)
             at oracle.dbtools.parser.plsql.TabCol.fetchColumns(TabCol.java:460)
             at oracle.dbtools.parser.plsql.TabCol.fetchColumns(TabCol.java:396)
             at oracle.dbtools.raptor.phighlight.StarAdvice.refresh(StarAdvice.java:98)
             at oracle.dbtools.raptor.phighlight.StarAdvice.starAdvice(StarAdvice.java:80)
             at oracle.dbtools.raptor.phighlight.SemanticAnalysis.doIt(SemanticAnalysis.java:178)
             at oracle.dbtools.raptor.phighlight.HighlightEditorPlugin.stateChanged(HighlightEditorPlugin.java:442)
             at oracle.dbtools.raptor.plsql.BackgroundParser.parse(BackgroundParser.java:284)
             at oracle.dbtools.raptor.plsql.BackgroundParser$1.construct(BackgroundParser.java:172)
             at oracle.dbtools.raptor.utils.NamedSwingWorker$2.run(NamedSwingWorker.java:115)
             at java.lang.Thread.run(Thread.java:748)
      • 1. Re: Import data by oracle sql developer 18.2
        thatJeffSmith-Oracle

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

         

        Is it a single CSV file with 1M records?

         

        How exactly are you doing the import with SQL Developer?

        • 2. Re: Import data by oracle sql developer 18.2

          When i try to import the data (1Milion rows) of a table it shows me an error and it only inserts 11183 rows.

          So the OBVIOUS thing to do is to test with a SMALL FILE.

           

          What were the results of that test?

           

          Break your large file into smaller pieces and import them.

          • 3. Re: Import data by oracle sql developer 18.2
            3756369

            Yes, i give you the steps i follow:

             

            I select the table-> right mouse button -> import Data

            Then I continue the wizard, select local file (csv with 1M records, without headers) - > next

            Then i use import method: insert (i've tried the other options but no result), in the file contents it shows me rigth order of the rows - > next

            All the columns are selected (on the right side) - > next

            Match by position, then some of the columns are specified: "Data is not compatible with column definition or is not available for a not nullable column" but by the moment I do not care (is for testing) - > next

            Then finish.

            When it starts it shows me:

             

            "Insert failed for rows  1  through  1000

            ORA-01400: cannot insert NULL into ("MYSCHEMA"."STATS"."ASSETNUMBER")

             

            Do you want to ignore all errors?

            Click yes to continue and ignore all errors.

            Click no to continue and prompt on error.

            Click cancel to cancel and rollback."

             

            I click yes and it takes some minutes untill finish: Executing insert for rows 1 to 1000... 1001 to 2000... untill 1M.

             

            Then it shows me that all rows are commited ("Import Data into table STATS from file stats.csv . Task successful and import committed.").

             

            Behind that display there is another error display showing:

             

            java.io.IOException: exception loading /tmp/Import-stats-csv-bad_5.sql

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                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:556)

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

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

                at oracle.ide.editor.EditorUtil.openDefaultEditorInFrame(EditorUtil.java:181)

                at oracle.dbtools.raptor.data.writers.ImportGen$1$1.run(ImportGen.java:522)

                at oracle.ide.util.IdeUtil$3.run(IdeUtil.java:1089)

                at oracle.bali.ewt.util.PaintUtils.invokeAfterRepaint(PaintUtils.java:53)

                at oracle.ide.util.IdeUtil.invokeAfterRepaint(IdeUtil.java:1102)

                at oracle.dbtools.raptor.data.writers.ImportGen$1.run(ImportGen.java:520)

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

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

                at java.awt.EventQueue.access$500(EventQueue.java:97)

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

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

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

                at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)

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

                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:205)

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

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

                at java.awt.WaitDispatchSupport$2.run(WaitDispatchSupport.java:190)

                at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:235)

                at java.awt.WaitDispatchSupport$4.run(WaitDispatchSupport.java:233)

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

                at java.awt.WaitDispatchSupport.enter(WaitDispatchSupport.java:233)

                at java.awt.Dialog.show(Dialog.java:1084)

                at java.awt.Component.show(Component.java:1671)

                at java.awt.Component.setVisible(Component.java:1623)

                at java.awt.Window.setVisible(Window.java:1014)

                at java.awt.Dialog.setVisible(Dialog.java:1005)

                at oracle.dbtools.raptor.backgroundTask.dialogs.TaskDialog.showDialogNow(TaskDialog.java:200)

                at oracle.dbtools.raptor.backgroundTask.dialogs.TaskDialog.access$100(TaskDialog.java:32)

                at oracle.dbtools.raptor.backgroundTask.dialogs.TaskDialog$DialogShower.run(TaskDialog.java:174)

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

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

                at java.awt.EventQueue.access$500(EventQueue.java:97)

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

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

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

                at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:80)

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

                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:205)

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

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

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

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

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

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

                at oracle.javatools.buffer.VectorBuffers.addBuffer(VectorBuffers.java:96)

                at oracle.javatools.buffer.EOLNormalizer.process(EOLNormalizer.java:157)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

                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:556)

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

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

                at oracle.ide.editor.EditorUtil.openDefaultEditorInFrame(EditorUtil.java:181)

                at oracle.dbtools.raptor.data.writers.ImportGen$1$1.run(ImportGen.java:522)

                at oracle.ide.util.IdeUtil$3.run(IdeUtil.java:1089)

                at oracle.bali.ewt.util.PaintUtils.invokeAfterRepaint(PaintUtils.java:53)

             

             

            And then, when i press ok, the error that i pasted at the beginning of the discussion is displayed on the terminal.

             

            Thanks in advance

            • 4. Re: Import data by oracle sql developer 18.2
              thatJeffSmith-Oracle

              >>java.io.IOException: exception loading /tmp/Import-stats-csv-bad_4.sql

              There are so many failures, we're using up all the memories to create or open the bad records.csv

               

              You loaded all the data you could, but dealing with the bad records created a problem.

               

              Is that file there ( /tmp/Import-stats-csv-bad_4.sql)?

               

              If so, open it in a text error or whatever and inspect, fix, and try again.

               

              if not, and you need the list, run the import again, but increase the amount of memory to the JVM. How big is the CSV file in megabytes?

              • 5. Re: Import data by oracle sql developer 18.2
                3756369

                Doing the test with small file is not an option because I'm trying to do stress test with large amount of data.

                 

                I could convert it to a 100 small files but it could take a lot of time (there are more tables with more than 1M rows) to do that, I cannot waste so much time with a test! I am sure that i am not the first one that is doing this kind of migration and there should be a solution provided by oracle

                 

                Thanks for your reply

                • 6. Re: Import data by oracle sql developer 18.2
                  thatJeffSmith-Oracle

                  Allocate more memory to the JVM

                   

                  Or

                   

                  Try a test where there isn't 980,000 errrors

                  • 7. Re: Import data by oracle sql developer 18.2

                    Doing the test with small file is not an option because I'm trying to do stress test with large amount of data.

                     

                    Sorry - but I disagree.

                     

                    Any experienced developer would ALWAYS test with a small file as part of the final stage of development in order to know that the process actually works properly.

                     

                    They would also test using a file that included some 'bad' data to make sure that was captured/reported properly.

                    I could convert it to a 100 small files but it could take a lot of time (there are more tables with more than 1M rows) to do that, I cannot waste so much time with a test!

                    Again - I have to STRONGLY disagree.

                     

                    What you shouldn't 'waste so much time' on is trying to repeatedly load such a big file before you have determined why the load is failing to begin with.

                     

                    It is TRIVIAL to write/download a file splitter to break a text file into multiple pieces.

                     

                    You don't need 100 files. You need to TEST with a small file to make sure your process really works.

                     

                    As Jeff pointed out you seem to have totally missed seeing the OUT OF MEMORY message that is causing the problem.

                     

                    And you still haven't indicated if you have now configured Sql Developer to use more memory. Have you? Tell us what you did to do that.

                    I am sure that i am not the first one that is doing this kind of migration and there should be a solution provided by oracle

                    No - you are NOT the first.

                     

                    And there are PLENTY of solutions provided by Oracle. For large files or a large number of files the recommended order would be:

                     

                    1. define and use an External Table - this option treats a file as if it were a table - you just QUERY the file the way you would a table.

                    SELECT * FROM myExternalTable

                    It doesn't use Java so there won't be any 'out of memory' issues

                     

                    External tables also support a PREPROCESSOR option that could break a large file into pieces if desired.

                     

                    You can also test a small amount of data by slightly modifying the above query to:

                    SELECT * FROM myExternalTable WHERE ROWNUM < 100

                    When you use the right tool for the job things can be REALLY simple.

                     

                    2. define and use a sql loader control file - this option would use sql*plus to do the load.

                     

                    It doesn't use Java so there won't be any 'out of memory' issues either

                     

                    3. Sql Developer - this is the option you choose. Unforturnately you don't seem to have read the User Guide to learn how to use it and have indicated you did NOT do any testing to learn if it would work for your use case.

                     

                    It uses Java and both Java and Sql Developer need to be configured and used properly when performing certain tasks.

                     

                    I suggest you adopt the methods that experts use and:

                     

                    1. ALWAYS read the documentation for products to learn what functionality is available and how to use it.

                    2. ALWAYS conduct tests to verify that the process you are using produces the correct results

                    3. ALWAYS analyze the test results for indications of any potential issues.

                     

                    The fault is NOT with Oracle or Sql Developer. It is solely YOUR responsibility.

                    • 8. Re: Import data by oracle sql developer 18.2
                      Mike Kutz

                      3756369 wrote:

                       

                      I've got error importing 1.000.000 of rows (csv type).

                       

                      I am currently testing Oracle exadata express and i am currently working with Oracle sql developer app to upload the data.

                      After loading a schema (migration from mysql) I would like to import data to the oracle database. To do that purpose I have all the table data in csv format and i use oracle sql developer to connect to the database.

                       

                       

                      Why are you NOT using SQL*Developer to do the migration?

                      (as in - SQL*Developer's Migration tools)

                       

                      MK

                      • 9. Re: Import data by oracle sql developer 18.2
                        3756369

                        thatJeffSmith-Oracle wrote:

                         

                        Allocate more memory to the JVM

                        I don't know how to configure that, is an option to avoid crashing sql developer tool. That out of memory is caused by the large amount of errors of the importing data process.

                         

                        Try a test where there isn't 980,000 errrors

                        Yes, this is the trick, I got a lot of errors due to: inserting '' (blank) data into varchar2 column type where that columns couldn't be null, (in mysql it's allowed) . So i configured the columns as nullable. Then it fix all the errors so i didn't get the crash and all the rows were commited.

                        Thank you

                        • 10. Re: Import data by oracle sql developer 18.2
                          3756369

                          Mike Kutz wrote:

                           

                          3756369 wrote:

                           

                          I've got error importing 1.000.000 of rows (csv type).

                           

                          I am currently testing Oracle exadata express and i am currently working with Oracle sql developer app to upload the data.

                          After loading a schema (migration from mysql) I would like to import data to the oracle database. To do that purpose I have all the table data in csv format and i use oracle sql developer to connect to the database.

                           

                           

                          Why are you NOT using SQL*Developer to do the migration?

                          (as in - SQL*Developer's Migration tools)

                           

                          MK

                          Because i couln't connect to the source db directly, then i tried another ways to do that.

                          Maybe i needed some migration manual and i couldn't find that.

                           

                          Thanks

                          • 11. Re: Import data by oracle sql developer 18.2
                            3756369

                            rp0428 wrote:

                             

                            Doing the test with small file is not an option because I'm trying to do stress test with large amount of data.

                             

                            Sorry - but I disagree.

                             

                            Any experienced developer would ALWAYS test with a small file as part of the final stage of development in order to know that the process actually works properly.

                             

                            They would also test using a file that included some 'bad' data to make sure that was captured/reported properly.

                            I could convert it to a 100 small files but it could take a lot of time (there are more tables with more than 1M rows) to do that, I cannot waste so much time with a test!

                            Again - I have to STRONGLY disagree.

                             

                            What you shouldn't 'waste so much time' on is trying to repeatedly load such a big file before you have determined why the load is failing to begin with.

                             

                            It is TRIVIAL to write/download a file splitter to break a text file into multiple pieces.

                             

                            You don't need 100 files. You need to TEST with a small file to make sure your process really works.

                             

                            As Jeff pointed out you seem to have totally missed seeing the OUT OF MEMORY message that is causing the problem.

                             

                            And you still haven't indicated if you have now configured Sql Developer to use more memory. Have you? Tell us what you did to do that.

                            I am sure that i am not the first one that is doing this kind of migration and there should be a solution provided by oracle

                            No - you are NOT the first.

                             

                            And there are PLENTY of solutions provided by Oracle. For large files or a large number of files the recommended order would be:

                             

                            1. define and use an External Table - this option treats a file as if it were a table - you just QUERY the file the way you would a table.

                            SELECT * FROM myExternalTable

                            It doesn't use Java so there won't be any 'out of memory' issues

                             

                            External tables also support a PREPROCESSOR option that could break a large file into pieces if desired.

                             

                            You can also test a small amount of data by slightly modifying the above query to:

                            SELECT * FROM myExternalTable WHERE ROWNUM < 100

                            When you use the right tool for the job things can be REALLY simple.

                             

                            2. define and use a sql loader control file - this option would use sql*plus to do the load.

                             

                            It doesn't use Java so there won't be any 'out of memory' issues either

                             

                            3. Sql Developer - this is the option you choose. Unforturnately you don't seem to have read the User Guide to learn how to use it and have indicated you did NOT do any testing to learn if it would work for your use case.

                             

                            It uses Java and both Java and Sql Developer need to be configured and used properly when performing certain tasks.

                             

                            I suggest you adopt the methods that experts use and:

                             

                            1. ALWAYS read the documentation for products to learn what functionality is available and how to use it.

                            2. ALWAYS conduct tests to verify that the process you are using produces the correct results

                            3. ALWAYS analyze the test results for indications of any potential issues.

                             

                            The fault is NOT with Oracle or Sql Developer. It is solely YOUR responsibility.

                            My tests with small tables was good, then i want test with big tables, so that was the purpose of my test

                             

                            I will take a note with your tips. I have not always all the time to do all the learning properly. I expected the migration should be easier

                             

                            Thanks

                            • 12. Re: Import data by oracle sql developer 18.2
                              thatJeffSmith-Oracle

                              Get access to the MySQL source. Then use the Migration wizard. We get the tables and data for you. It's pretty straightforward.