1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using dbms_metadata to extract DDL of all DB objects

Discussion in 'SQL PL/SQL' started by doha, Jun 23, 2009.

  1. doha

    doha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    this is my first post on this website, and i hope i can find a solution for my problem :)
    well, i have to extract DLL of all database objects so that we can use the script for migration...
    the problem is, i have a very big DB with at least 3000 tables :s and i have to manage the order of tables because of foreign keys.... that link to other tables that have to be extracted before...

    please, can you help to do that with dbms_metadata?????
    thank you very much!!
     
  2. oralover2006

    oralover2006 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    75
    why not using TOAD or like-wise utility for this purpose???

    better search with google for "dbms_metadata" ( with double quotes )
     
  3. doha

    doha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    thanks for your reply, i've already search on google and ound nothing :s
    doest TOAD extract DLL of all DB objects? respecting table orders?
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Welcome to Club oracle...

    I understand the concern. One option will be to do it on following steps

    1. Extract all the DDL for tables (without Foreign keys)
    2. Extract the foreigh key definition separately
    3. Load the DDL extracted for creating table
    4. Apply the foreign key by executing the foreign key extract , once all the tables are created in step 3.


    There One more routine called set_transform_param in dbms_metadata package which can be used to turn off some related objects while extracting the DDL for tables.

    Some thing like this.

    Code (SQL):

    dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS', FALSE);
     
    Then the DDL extracted for tables wont have foreign key DDL

    Then Extract Foreign key DDL seperately like ,

    Code (SQL):

    DBMS_METADATA.GET_DDL('REF_CONSTRAINT','<REF_CONS_NAME>');
     
     
  5. doha

    doha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    that's a very good idea! i'll try that and keep you informed

    really thank you!!!!

    Doha :)
     
  6. doha

    doha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    thank you very much :)

    it's working ...