Scripts to Reverse Engineer Database DDL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Scripts to Reverse Engineer Database DDL

Hybrid View

  1. #1
    Join Date
    Apr 2000
    Location
    Charlotte, NC, USA
    Posts
    48
    Hi,

    Looking for a script that I can run against a database that will reverse engineer the DDL statement for the object in it. I don't have the opportunity to plug in a data modeling tool to do the job.

    Any guidance is appreciated.

    Thanx

  2. #2
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    if you have toad or other tools they also create script for existing tables

  3. #3
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    I like the designer. With 6i you can all kinds of fancy things.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  4. #4
    Join Date
    Jun 2000
    Posts
    117
    The latest TOAD with DBA module has a create schema option that will crteate the DDL for you.

    E. Yen
    OCP DBA - 8, 8i, 9i

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You will find some very good and useful scripts here.
    http://www.think-forward.com/sqltips.htm

    Sanjay

  6. #6
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    I use the 6.4 version of Toad and it has SQLModeler, Schema Browser etc and it is pretty good.
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  7. #7
    Join Date
    Apr 2001
    Posts
    107
    Hope this can help:
    Never tried it myself...

    -----------------------------------------------------
    Rem Filename : Generate_createdb.sql
    Rem Purpose : To reverse engineer the createdb.sql from a running database
    Rem Syntax : sqlplus -s / @Generate_createdb.sql
    Rem produces an output called createdb_$ORACLE_SID.sql
    Rem
    Rem Notes : Developed and tested on Oracle 7.3.3 for Solaris
    Rem Autoextending files NOT catered for
    Rem Runs ONLY catproc.sql and catalog.sql
    Rem Assumes TEMPORARY tablespace is TEMP
    Rem Non UNIX databases may need small modifications to the paths
    Rem of called scripts i.e. catalog.sql, catproc.sql ...
    Rem
    Rem History : 20-01-2000 E Augustine Created
    Rem 14-02-2000 E Augustine
    Rem Database is created in NOARCHIVELOG and then altered
    Rem to ARCHIVELOG if originally configured in ARCHIVELOG.
    Rem

    Set TERM On SERVEROUTPUT On Size 10000 FEEDBACK Off LINESIZE 300 TRIMSPOOL On
    Spool createdb_${ORACLE_SID}.sql

    Declare

    -- Non SYSTEM Tablespaces are created explicitly.
    -- The Min(FILE_ID) is used to select the order in which Tablespaces
    -- were created.
    Cursor C_Tablespaces Is
    Select TABLESPACE_NAME, Min(FILE_ID) From DBA_DATA_FILES
    Where TABLESPACE_NAME Not Like 'SYSTEM%'
    Group By TABLESPACE_NAME
    Order By 2;

    -- All files for a tablespace.
    Cursor C_Datafiles ( P_TS Varchar2 ) Is
    Select * From DBA_DATA_FILES
    Where TABLESPACE_NAME = P_TS
    Order By FILE_ID;

    -- Tablespace information
    Cursor C_Tablespace_Info ( P_TS Varchar2 ) Is
    Select * From DBA_TABLESPACES
    Where TABLESPACE_NAME = P_TS;

    -- SYSTEM rollback segment is created implicitly hence not selected.
    Cursor C_Rollback_Segs ( P_TS Varchar2 ) Is
    Select * From DBA_ROLLBACK_SEGS
    Where TABLESPACE_NAME = P_TS
    And SEGMENT_NAME <> 'SYSTEM'
    Order By SEGMENT_ID;

    Cursor C_Logfile ( L_GROUP_NUM In Varchar2 ) Is
    Select A.Group#, A.Bytes, A.Members, B.Member
    From V$LOG A, V$LOGFILE B
    Where A.Group# = B.Group#
    And A.Group# = L_GROUP_NUM
    Order By A.Group#;

    FirstTime Boolean;
    L_LINE Varchar2(2000); -- A line to output
    L_SID Varchar2(20); -- Oracle Database Name
    L_LOGMODE Varchar2(50); -- Archivelog or not
    L_CHRSET Varchar2(50); -- NLS character set
    L_GROUPS Number(9); -- Nos of Log Groups
    L_LOGSIZE Number(9); -- Logfile size
    L_MEMBERS Number(9); -- Nos Members per Group
    L6 Varchar2(9); -- 6 spaces
    L4 Varchar2(9); -- 4 spaces
    L2 Varchar2(9); -- 2 spaces

    Begin
    L2 := ' ';
    L4 := L2||L2;
    L6 := L2||L4;

    L_LINE := 'Connect Internal'||Chr(10)||
    'Set TERMOUT On ECHO On'||Chr(10)||
    'Spool createdb_${ORACLE_SID}.log'||Chr(10)||
    'Startup nomount pfile=../pfile/init_0.ora'||Chr(10);

    dbms_output.put_line(L_LINE);

    Select NAME, LOG_MODE
    Into L_SID, L_LOGMODE
    From V$DATABASE;

    Select VALUE Into L_CHRSET
    From V$NLS_PARAMETERS
    Where Upper(PARAMETER) = 'NLS_CHARACTERSET';

    Select Max(GROUP#), Max(MEMBERS), Max(BYTES)/1024
    Into L_GROUPS, L_MEMBERS, L_LOGSIZE
    From V$LOG;

    L_LINE := 'Create Database "'|| L_SID ||'"'|| Chr(10)||
    L4||'Maxlogfiles '||To_Char(L_GROUPS*L_MEMBERS*4)||Chr(10)||
    L4||'Maxlogmembers '||To_Char(L_MEMBERS*2)||Chr(10)||
    L4||'Maxloghistory 160'||Chr(10)||
    L4||'Maxdatafiles 255'||Chr(10)||
    L4||'Maxinstances 1'||Chr(10)||
    L4||'NOARCHIVELOG'||Chr(10)||
    L4||'Character Set "'||L_CHRSET||'"'||Chr(10)||
    L4||'Datafile';

    dbms_output.put_line(L_LINE);

    --
    -- Get the datafiles for the SYSTEM tablespace
    --
    FirstTime := TRUE;
    For Rec_Datafiles In C_Datafiles ( 'SYSTEM' ) Loop

    If FirstTime Then
    FirstTime := FALSE;
    L_LINE := ' ';
    Else
    L_LINE := L_LINE||' ,'||Chr(10)||' ';
    End If;

    L_LINE := L_LINE||
    ''''||Rec_Datafiles.FILE_NAME||''''||' Size '||
    To_Char(Rec_Datafiles.BYTES/1024)||' K';
    End Loop;

    L_LINE := L_LINE || Chr(10) || L4 || 'Logfile ';
    dbms_output.put_line(L_LINE);

    --
    -- Create the LOGFILE bits ...
    --

    FirstTime := TRUE; -- For groups
    For L_INDEX In 1.. L_GROUPS Loop

    If FirstTime Then
    FirstTime := FALSE; -- For groups
    L_LINE := ' Group '|| To_Char(L_INDEX) || ' (';
    Else
    L_LINE := ' ,Group '|| To_Char(L_INDEX) || ' (';
    End If;

    FirstTime := TRUE; -- For members

    For Rec_Logfile In C_Logfile ( L_INDEX ) Loop

    If FirstTime Then
    FirstTime := FALSE; -- For members
    Else
    L_LINE := L_LINE ||Chr(10)||L6||L2||' ,';
    End If;

    L_LINE := L_LINE||''''||Rec_Logfile.MEMBER||'''';

    End Loop;

    L_LINE := L_LINE || Chr(10)||L6||L2||
    ' ) Size '||To_Char(L_LOGSIZE)||' K ';
    dbms_output.put_line(L_LINE);
    End Loop;

    --
    -- The bootstrapping stuff ...
    --

    L_LINE := '/'||Chr(10)||Chr(10)||
    'Set TERMOUT Off ECHO Off'||Chr(10)||
    '@?/rdbms/admin/catalog.sql'||Chr(10)||
    '@?/rdbms/admin/catproc.sql'||Chr(10)||
    'Set TERMOUT On ECHO On'||Chr(10)||Chr(10);

    dbms_output.put_line(L_LINE);

    --
    -- The Rollback segments in the SYSTEM tablespace
    --

    For Rec_RBS In C_Rollback_Segs ('SYSTEM') Loop

    L_LINE := 'Create Rollback Segment '||Rec_RBS.SEGMENT_NAME||
    Chr(10)||' Tablespace '||
    Rec_RBS.TABLESPACE_NAME||
    Chr(10)||' '||'Storage (';

    L_LINE := L_LINE||Chr(10)||' Initial '||
    To_Char(Rec_RBS.INITIAL_EXTENT/1024)||' K'||
    Chr(10)||' Next '||
    To_Char(Rec_RBS.NEXT_EXTENT/1024)||' K'||
    Chr(10)||' Minextents '||
    To_Char(Rec_RBS.MIN_EXTENTS)||
    Chr(10)||' Maxextents '||
    To_Char(Rec_RBS.MAX_EXTENTS)||
    Chr(10)||' Optimal '||
    To_Char(Rec_RBS.MIN_EXTENTS *
    Rec_RBS.NEXT_EXTENT/1024)||' K'||Chr(10)
    ||' )'||Chr(10)||'/'||Chr(10)
    ;
    dbms_output.put_line(L_LINE);

    End Loop;

    --
    -- Create all other tablespaces ...
    --

    For Rec_Tablespaces In C_Tablespaces Loop

    L_LINE := 'Create Tablespace '||Rec_Tablespaces.TABLESPACE_NAME;

    dbms_output.put_line(L_LINE);

    FirstTime := TRUE;
    For Rec_Datafiles In
    C_Datafiles ( Rec_Tablespaces.TABLESPACE_NAME ) Loop

    If FirstTime Then
    FirstTime := FALSE;
    L_LINE := ' Datafile ';
    Else
    L_LINE := ' ,';
    End If;

    L_LINE := L_LINE||''''||Rec_Datafiles.FILE_NAME||''''||
    ' Size '||
    To_Char(Rec_Datafiles.BYTES/1024)||' K';
    dbms_output.put_line(L_LINE);

    End Loop;


    For Rec_TS_Info In
    C_Tablespace_Info( Rec_Tablespaces.TABLESPACE_NAME ) Loop

    L_LINE :=' Default Storage ( '||
    Chr(10)||' Initial '||
    To_Char(Rec_TS_Info.INITIAL_EXTENT/1024)||' K'||
    Chr(10)||' Next '||
    To_Char(Rec_TS_Info.NEXT_EXTENT/1024)||' K'||
    Chr(10)||' Minextents '||
    To_Char(Rec_TS_Info.MIN_EXTENTS)||
    Chr(10)||' Maxextents '||
    To_Char(Rec_TS_Info.MAX_EXTENTS)||
    Chr(10)||' Pctincrease '||
    To_Char(Rec_TS_Info.PCT_INCREASE)||
    Chr(10)||
    ' )'
    ;

    L_LINE := L_LINE||Chr(10)||' '||
    Rec_TS_Info.CONTENTS||Chr(10)||'/'||Chr(10)||Chr(10);
    dbms_output.put_line(L_LINE);

    End Loop;

    --
    -- Create all Rollback segments in the tablespace being created ...
    --

    For Rec_RBS In C_Rollback_Segs (Rec_Tablespaces.TABLESPACE_NAME) Loop

    L_LINE := 'Create Rollback Segment '||Rec_RBS.SEGMENT_NAME||
    Chr(10)||' Tablespace '||
    Rec_RBS.TABLESPACE_NAME||
    Chr(10)||' '||'Storage (';

    L_LINE := L_LINE||Chr(10)||' Initial '||
    To_Char(Rec_RBS.INITIAL_EXTENT/1024)||' K'||
    Chr(10)||' Next '||
    To_Char(Rec_RBS.NEXT_EXTENT/1024)||' K'||
    Chr(10)||' Minextents '||
    To_Char(Rec_RBS.MIN_EXTENTS)||
    Chr(10)||' Maxextents '||
    To_Char(Rec_RBS.MAX_EXTENTS)||
    Chr(10)||' Optimal '||
    To_Char(Rec_RBS.MIN_EXTENTS *
    Rec_RBS.NEXT_EXTENT/1024)||' K'||Chr(10)
    ||' )'||Chr(10)||'/'||Chr(10)
    ;
    dbms_output.put_line(L_LINE);

    End Loop;

    End Loop;

    L_LINE := 'Alter User SYS Temporary Tablespace TEMP'||
    Chr(10)||'/'||Chr(10)||
    'Alter User SYSTEM Default Tablespace TOOLS '||Chr(10)||
    'Temporary Tablespace TEMP'||
    Chr(10)||'/'||Chr(10)||Chr(10);

    dbms_output.put_line(L_LINE);

    L_LINE := 'Connect System/Manager'||Chr(10)||
    'Set TERMOUT Off ECHO Off'||Chr(10)||
    '@?/rdbms/admin/catdbsyn'||Chr(10)||
    '@?/sqlplus/admin/pupbld'||Chr(10)||
    'Set TERMOUT On ECHO On'||Chr(10);

    dbms_output.put_line(L_LINE);

    L_LINE := 'Connect Internal'||Chr(10)||
    'Shutdown'||Chr(10)||
    'Startup'||Chr(10)||Chr(10);

    dbms_output.put_line(L_LINE);

    If L_LOGMODE = 'ARCHIVELOG' Then
    L_LINE := 'Shutdown immediate'||Chr(10)||
    'Startup Mount'||Chr(10)||
    'Alter Database ARCHIVELOG;'||Chr(10)||
    'Alter Database Open;'||Chr(10);
    dbms_output.put_line(L_LINE);
    End If;

    L_LINE := 'Create User OPS$ORACLE Identified Externally'||Chr(10)||
    'Default Tablespace TOOLS'||Chr(10)||
    'Temporary Tablespace TEMP'||Chr(10)||'/'||Chr(10)||
    'Grant DBA To OPS$ORACLE'||Chr(10)||'/'||Chr(10)||Chr(10);

    dbms_output.put_line(L_LINE);

    L_LINE := 'Spool Off'||Chr(10)||'Exit'||Chr(10);

    dbms_output.put_line(L_LINE);
    End;
    /
    Spool Off
    Exit
    ---------------------------------------------------

    Cheers

    Fabien

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    In 9i

    - A new PL/SQL package, DBMS_METADATA.GET_DDL, allows you to obtain metadata (in the form of DDL used to create the object) about a schema object.

  9. #9
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    Thumbs down

    Hello. Tried running the given script and received message:
    Bind variable "GENERATE_CREATEDB" not declared.

    any ideas?
    "High Salaries = Happiness = Project Success."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width