3 Replies Latest reply: Jul 2, 2013 7:53 PM by rp0428 RSS

    SQL Developer 3.2 - Export DDL challenge

    1008686

      Hi,

       

      I would like to Export DDL for approximately 300 of 1000 objects in a schema.  I have the names of all required tables for which I'd like to get the DDL in a table in my personal schema.  Is there a way that I can use this table as a driver for the built-in Export DDL utility or will I need to either go to the schema browser and hand-pick each of the 300 tables and/or from the Tools-Export DDL "Specify Objects" window?

       

      I would like to make this more automated so that I dont have to keep clicking, scrolling, clicking my way through the list of required objects.  Any thoughts are appreciated, thanks.

        • 1. Re: SQL Developer 3.2 - Export DDL challenge
          rp0428

          1008686 wrote:

           

          Hi,

           

          I would like to Export DDL for approximately 300 of 1000 objects in a schema.  I have the names of all required tables for which I'd like to get the DDL in a table in my personal schema.  Is there a way that I can use this table as a driver for the built-in Export DDL utility or will I need to either go to the schema browser and hand-pick each of the 300 tables and/or from the Tools-Export DDL "Specify Objects" window?

           

          I would like to make this more automated so that I dont have to keep clicking, scrolling, clicking my way through the list of required objects.  Any thoughts are appreciated, thanks.

          There is no way to use sql developer to do that.

           

          You can:

           

          1. do it manually as you suggest

          2. do it manually by writing a script that makes the appropriate DBMS_METADATA calls

          3. use expdp to extract the metadata and create a DDL file.

           

          The full DDL for a table will include a lot of components that many people don't even want, for example storage clauses.

           

          The bigger issue you should address is why you don't already have the DDL to begin with. Best practices are to create the DDL and keep it in a version control system; not extract it after the fact.

           

          I suggest you use the EXPDP utility to extract the DDL into a file so that you have it for future use.

           

          If you plan to write a script there are plenty of examples on the web that show how to do that. Here is one:

          http://www.colestock.com/blogs/2008/02/extracting-ddl-from-oracle-2-approaches.html

          • 2. Re: SQL Developer 3.2 - Export DDL challenge
            1008686

            Hi,

             

            Thanks for the reply.  Regarding your 2nd suggestion to use DBMS_METADATA....I have tried this many times in the past and have had issues with words getting truncated or carriage returns getting inserted in seemingly random places.  I have found this to be unreliable but I will take a closer look at the examples provided in the attachment.  I have also used the expdp METADATA_ONLY option on many occasions but have found the data pump DDL export to be much slower than an export of the same objects in SQL Developer.

             

            The basis of my original question came from the need to recreate production objects in a lower development environment.  We do not keep our production DDL in a version control system but I would be interested to know what others are using to do this.

             

            Thanks again.

            • 3. Re: SQL Developer 3.2 - Export DDL challenge
              rp0428

               

              Regarding your 2nd suggestion to use DBMS_METADATA....I have tried this many times in the past and have had issues with words getting truncated or carriage returns getting inserted in seemingly random places.
              . . .

              The basis of my original question came from the need to recreate production objects in a lower development environment.  We do not keep our production DDL in a version control system but I would be interested to know what others are using to do this.

               

               

              I have used DBMS_METADATA for several years and have NEVER had any issue with words getting truncated or random insertions.

              My guess is that if that happens it is because of the way you are manipulating the CLOBs that you get in the result set. Certainly if you use sql*plus and try to display a CLOB in a command window or spool it and haven't set the line length and other settings properly you can cause sql*plus to format the output.

               

              Oracle's own export/import and expdp/impdp use DBMS_METADATA so the chances of that package being the cause of the issues you reported are 'slim and none'.

               

              Best practices are to keep your DDL in a version control system. Even if your org doesn't keep it in a VCS they should certainly be keeping the DDL in text form somewhere. Relying on being able to extract it properly from the DB when you need it is both risky and unprofessional. There is too great a chance for someone to make accidental changes that go unnnoticed and uncorrected.

               

              In your case I would recommend that you use BOTH options: 1) get an export of ALL DDL and test an import of it to ensure you exported it properly and 2) use DBMS_METADATA to get a TEXT version of the DDL to seed your intial use of a VCS.