This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 26, 2013 10:10 AM by vlethakula RSS

Extract DDL for all objects separately !

AnkitV Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    expdp user/password DIRECTORY=<dmpdir> DUMPFILE=<.dmp> content=METADATA_ONLY
  • 3. Re: Extract DDL for all objects separately !
    TSharma-Oracle Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    Use SQL Developer, Tools menu, Database Export option. Why re-invent the wheel ?

    Raj
  • 12. Re: Extract DDL for all objects separately !
    TSharma-Oracle Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points