1 2 Previous Next 16 Replies Latest reply: Feb 26, 2013 12:10 PM by vlethakula RSS

    Extract DDL for all objects separately !

    AnkitV
      Hi All

      I have a huge DB having 3000 tables (having indexes,triggers,synonym), 1200 functions/procedures, 1000 views etc.
      I need to extract only metadata and put into a version control repository.

      I need to extract all objects (tables,procs,functions,views) in separate files, with the grants for each procs/func/table/view in the respective procs/func/table/view file only.
      Indexes should be in respective table file only. Each object (procs/func/table/view etc) should have DROP command in the beginning.

      I tried sql developer, but it gives separate files for grants,indexes,triggers,drops etc and also gives option to generate one script for all tables, one for all views, one for all indexes and so on, so doesn't satisfy my requirement.

      Please suggest me a good tool (preferably FREE one) to extract the metadata in said fashion.

      Thanks.
        • 1. Re: Extract DDL for all objects separately !
          sb92075
          AnkitV wrote:
          Hi All

          I have a huge DB having 3000 tables (having indexes,triggers,synonym), 1200 functions/procedures, 1000 views etc.
          I need to extract only metadata and put into a version control repository.

          I need to extract all objects (tables,procs,functions,views) in separate files, with the grants for each procs/func/table/view in the respective procs/func/table/view file only.
          Indexes should be in respective table file only. Each object (procs/func/table/view etc) should have DROP command in the beginning.

          I tried sql developer, but it gives separate files for grants,indexes,triggers,drops etc and also gives option to generate one script for all tables, one for all views, one for all indexes and so on, so doesn't satisfy my requirement.

          Please suggest me a good tool (preferably FREE one) to extract the metadata in said fashion.

          Thanks.
          DBMS_METADATA.GET_DDL
          • 2. Re: Extract DDL for all objects separately !
            Fran
            expdp user/password DIRECTORY=<dmpdir> DUMPFILE=<.dmp> content=METADATA_ONLY
            • 3. Re: Extract DDL for all objects separately !
              TSharma-Oracle
              You can use export/Import or Datapump utilities. If you still want to use some third party tool, you can go with TOAD. In TOAD you can specify that you need an out put in a singlefile. Trial version is free I believe.

              http://www.quest.com/toad-for-oracle/software-downloads.aspx
              • 4. Re: Extract DDL for all objects separately !
                AnkitV
                but this will give me single dump file, i want separate files via some automatic script generation mechanism
                • 5. Re: Extract DDL for all objects separately !
                  sb92075
                  AnkitV wrote:
                  but this will give me single dump file, i want separate files via some automatic script generation mechanism
                  You need to write script that meets your requirements.
                  • 6. Re: Extract DDL for all objects separately !
                    dbthought
                    Correct sb,

                    If anyone want specific customized script , you need to write your own script , and that can be use as tool.

                    Thanks
                    • 7. Re: Extract DDL for all objects separately !
                      Fran
                      then follow sb92075 recommendation:

                      select DBMS_METADATA.GET_DDL('INDEX','index_name') from DUAL;

                      or tables, or views.... You have to do a script to not insert Index_name, table_name, ..... everytime.
                      • 8. Re: Extract DDL for all objects separately !
                        vlethakula
                        set long 10000
                        SELECT dbms_metadata.get_ddl(replace(OBJECT_TYPE, ' ', '_'), OBJECT_NAME,OWNER)
                        FROM DBA_OBJECTS
                        WHERE OBJECT_TYPE in ('SEQUENCE',
                        'PROCEDURE',
                        'DATABASE LINK',
                        'PACKAGE',
                        'PACKAGE BODY',
                        'MATERIALIZED VIEW',
                        'TABLE',
                        'INDEX',
                        'VIEW',
                        'FUNCTION')
                        AND
                        OWNER = 'SCOTT';
                        • 9. Re: Extract DDL for all objects separately !
                          sb92075
                          Dev wrote:
                          Correct sb,

                          If anyone want specific customized script , we need to write our own script , and that can be use as tool.

                          Thanks
                          are you & AnkitV     team mates (we?)?

                          This should be a one off task to populate code repository.
                          • 10. Re: Extract DDL for all objects separately !
                            riedelme
                            AnkitV wrote:
                            Hi All

                            I have a huge DB having 3000 tables (having indexes,triggers,synonym), 1200 functions/procedures, 1000 views etc.
                            I need to extract only metadata and put into a version control repository.

                            I need to extract all objects (tables,procs,functions,views) in separate files, with the grants for each procs/func/table/view in the respective procs/func/table/view file only.
                            Indexes should be in respective table file only. Each object (procs/func/table/view etc) should have DROP command in the beginning.
                            The hard part is the requirement of separating the items into different files.

                            sb2075's answers are your best option. Write a PL/SQL script on the server to use DBMS_METADATA.GET_DDL or whatevever equivalent extraction routine will work for you. Loop through the objects you need from the data dictionary and writing the data using UTL_FILE. The simplified logic should look something like
                            foreach table
                              get the ddl
                              generate filename
                              open file
                              write ddl
                              close file
                            >
                            I tried sql developer, but it gives separate files for grants,indexes,triggers,drops etc and also gives option to generate one script for all tables, one for all views, one for all indexes and so on, so doesn't satisfy my requirement.
                            You don't want to manually use SQL*Developer to do 3000 extractions anyways. The script should do it all for you.
                            • 11. Re: Extract DDL for all objects separately !
                              User286067
                              Use SQL Developer, Tools menu, Database Export option. Why re-invent the wheel ?

                              Raj
                              • 12. Re: Extract DDL for all objects separately !
                                TSharma-Oracle
                                Again if you are scared of writing PL/SQL procedure and do not want to use Oracle utilities. Your best option is TOAD.
                                In TOAD you can specify that you need an out put in a singlefile OR you can mention one file per object. Trial version is free I believe.You can do this in one shot.

                                http://www.quest.com/toad-for-oracle/software-downloads.aspx
                                • 13. Re: Extract DDL for all objects separately !
                                  riedelme
                                  rjamya wrote:
                                  Use SQL Developer, Tools menu, Database Export option. Why re-invent the wheel ?

                                  Raj
                                  The OP wants the DDL for every table in different files. How do you put the output from export, datapump, etc. in different files?

                                  SQL*Developer can of course save to different files but I would not want to do it manually one at a time for 3000 tables.
                                  • 14. Re: Extract DDL for all objects separately !
                                    Osama_Mustafa
                                    the best solution as i see and already mentioned by Sb is create your own scripts to do what you need.
                                    1 2 Previous Next