6 Replies Latest reply on May 8, 2015 4:47 PM by jgarry

    sql developer - DB Export options


      Hi guys

      I have a question for you about the export of a database with oracle SQL developer.

      Let me explain the problem: I had to create my database application from one PC to another, I then performed full export (tables, views and data) in a single file export.sql. When I started the script on the destination PC, I noticed that many mistakes have been raised. By analyzing the code of the file export.sql, I noticed that SQL developer, listed in a disorderly objects to be exported, in particular, has placed first scripts for table creation, then those for the data, then constraints and PK then the constraints of FK, creating problems with the referential integrity constraints.

      As a solution to previous errors, I had to run an export file sql representatives each one divided by the creation of the single table, the associated PK, FK and data associated with it. I then performed manually every single script, following an order from the constraints of addiction related to FK. In this way, I rebuilt the DB without errors on the destination PC, but it's an operation was costly in terms of time.

      I ask you: is there an option in SQL developer tool to export, that makes it possible to create a script, in which the tables are created, always with their PK FK and data, in the order dictated by dependencies in order to prevent them from being violated referential integrity constraints?

      Sorry for my english.

      See ya!

        • 1. Re: sql developer - DB Export options
          John Stegeman

          Use data pump.


          I believe that SQL Developer has a menu item somewhere to run it, or you can run from the command line or from the PL/SQL API.

          • 2. Re: sql developer - DB Export options

            Which version of sqldeveloper?

            • 3. Re: sql developer - DB Export options

              If you're using the later version of SQL Developer you can use the data modeller to generate the DDL for all schemas / objects in order (just pull down the latest 4.x release).


              SQLDeveloper -> File -> Data Modeller -> Export -> DDL file


              Then choose your schema, objects, dependencies. This will generate tables, indexes then constraints & also your packages. Handy for exporting to a model too. I'd probably go with Johns recommendation though, datapump is quicker & cleaner for a full DB dump.

              • 4. Re: sql developer - DB Export options

                thanks guys for the answers!

                @John Stegeman: I found the option to export data pump for SQL Developer. I post a screenshot.


                A question, but for this procedure and exports, you are not considered the views and triggers? What if I want to extract these items?

                I have a problem though, at the end of the wizard, when I click the button end, this exception is raised

                ORA-31626: job does not exist.

                  I do not understand.

                @jgarry: the version of sql developer is

                @cobert: I'm trying SQLDeveloper -> File -> Data Modeller -> Export -> DDL files, but I have to follow some tutorials, because I do not know to complete.

                • 5. Re: sql developer - DB Export options

                  Absolutely, whichever is cleanest & gives the best results

                  More detailed instructions below, first you need to pull in the definition you're looking for:



                  File -> Data Modeller -> Import Data Dictionary -> Connect to your Database

                  -> choose the schema(s) that you want & select all objects

                  -> click finish (this will then generate your dictionary model & your design)


                  You can then export your definitions to a DDL file



                  File -> Data Modeller -> Export Data (you will be presented with your connection details)

                  -> choose generate

                  -> then in the create selection, check that it says "regular DDL" and click OK.


                  This should then generate your DDL file in order.

                  • 6. Re: sql developer - DB Export options

                    Note that thatjeffsmith.com is very useful.