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!

Generating Oracle DB creation script when the DB is up and running

Discussion in 'General' started by abirami.thirunavukkarasu, Aug 13, 2012.

  1. Hi,

    I am using Oracle 11g and almost 3-4 months ago, I had created the database while installation but had not stored the DB creation script. But now I need to get the Oracle DB creation script. Is it possible to get the exact DB creation script without bringing down the DB.

    I want the exact structure and the other parameters like NLS_PARAMS , size etc. So that using the same script I can create the databse in another machine.

    For the data and other details, I will perform an export/import. But i need the exact structure script of the old database.

    Please advise.

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You do know that a full database export will create users, datafiles and tablespaces which is what the scripts would do had you saved them before creating the database? There is an option to create scripts in the DBCA interface prior to creating the database -- did you not notice this?

    You CAN generate the same ddl you'd use to create the database using the dbms_metadata package:

    Code (SQL):
    CREATE TABLE ddl (ddl CLOB, seqno NUMBER);
    DECLARE
     openHandle NUMBER;
     transHandle NUMBER;
     DDLs sys.ku$_ddls;
     DDL sys.ku$_ddl;
     seqNo NUMBER:=1;
    BEGIN
     openHandle := dbms_metadata.OPEN('DATABASE_EXPORT');
     transHandle := dbms_metadata.add_transform(openHandle, 'DDL');
     dbms_metadata.set_transform_param(transHandle,'SQLTERMINATOR', TRUE);
     
     loop
        DDLs := dbms_metadata.fetch_ddl(openHandle);
           exit WHEN DDLs IS NULL;
      FOR i IN 1..DDLs.COUNT loop
          DDL := DDLs(i);
      INSERT INTO ddl VALUES(DDL.ddltext, seqNo);
          seqNo := seqNo + 1;
      END loop;
     END loop;
    END;
    /

     
    This is, of course, the same DDL you'd get from exporting the database. The PL/SQL block populates the DDL table (created in the first line of the code) so you would query the DDL column in the DDL table (yes, I know it's redundant) to get the statements to create a copy database from your original -- make certain you order by seqno to get everything run in the correct order.
     
  3. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Hi David,

    Thanks a lot for sharing such wonderful tip and source code. I assumed that code should need to be executed by sysdba because we use variables as sys. Does it correct?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I don't understand the 'we use variables as sys' comment. You run this as SYSDBA so you can get the DDL for the database. No other user or privilege will be able to fetch that content.