Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> free: format export files and gen all the ddl (users, tablespaces, tables,grants, packages...)

free: format export files and gen all the ddl (users, tablespaces, tables,grants, packages...)

From: Steve Perry <sperry_at_sprynet.com>
Date: Sun, 17 Oct 1999 20:23:13 -0500
Message-ID: <7udskq$107$1@nntp2.atl.mindspring.net> 





I hope this helps somebody. A bunch of you have helped me in the past.
I always wanted to know why Oracle never provided a utility to extract the
ddl from an export file. Over the years, I've seen numerous questions in the
past including my own. I also wanted to learn perl. I learned just enough to
be dangerous and wrote this script. It should work as well on Unix as it
does on NT (NT users need to install Activestate's perl).
I have scripts to reverse engineer tables/grants, but as Oracle adds more
and more features I wonder if I get everything. This allows me to get
everything.



INSTRUCTIONS FOR USE



save off the code below to a file. (my isp would NOT allow me to upload any
file to this group).


export your database (rows=N) so you have just the schema information
import the export file (show=y) so you get a log file.
ftp the log file to your workstation in the same directory as the perl
script


on NT enter:  export_buddy.pl <logfile>
on 98 enter: perl export_buddy.pl junk.log
after it's done processing, it should load the output file in notepad or
wordpad


NOTE: if you run it on unix, you'll probably want to comment out the
"system" call at the end



CAVEATS



I've only used it on NT and 98, but it should work as well on Unix
For big databases, this can generate several meg of output and, on Windows,
can bog down the editor


It's main purpose is as a disaster tool if somebody deletes their code, I
plan on putting it in cron on a nightly/weekly basis
I've only used bits and pieces from the generated file and never used the
whole thing to recreate a database.


I haven't done a lot of testing, but it works ok for me (real perl
programmers could really improve the code I think).
Code (procedures, functions, packages...) sometimes doesn't get formatted
properly (lines don't get split)


Play around with it on a TEST database before you start betting your job on
it :)



Let me know if I missed something, or email any fixes to me.



Steve


-----------code below -----------------------------------------


## -------------------------------------------------------------------------




##


#  EXPORT BUDDY (PART OF 'BUDDY' SUITE)



#  DESIGNED BY AND FOR REALLY, REALLY POOR DBA'S... :)



## -------------------------------------------------------------------------


##


#    FILE: EXPORT_BUDDY.PL



#  DEPENDENT: ACTIVESTATE'S PERL (WWW.ACTIVESTATE.COM)



#   USAGE: EXPORT_BUDDY.PL <LOG FILE FROM IMPORT USING SHOW=Y PARM>


#  AUTHOR: S. PERRY (sperry_at_sprynet.com)


#    DATE: 10/1999



#  DESCRIPTION:



#  EXPORT BUDDY WAS WRITTEN AS A CHEAP WAY TO GET THE FULL DDL OF A DATABASE



#  EXPORT FILE. IT'S WRITTEN AS A FALLBACK METHOD AND INFORMATIONAL IF YOU



#  DON'T WANT TO TRY AND WRITE PL/SQL TO REVERSE ENGINEER YOUR DATABASE.



#


#  OPEN ISSUES:



#  HAVEN'T FIGURED OUT A GOOD WAY TO GUARANTEE



PACKAGES/FUNCTIONS/VIEWS..(CODE)



#  WILL GET FORMATTED PROPERLY.



#


## -------------------------------------------------------------------------


#  MAINTENANCE



#  WHO     WHEN      WHY



#  SMP    10/09/99   CREATED



## -------------------------------------------------------------------------


#





#Open/Test Input File and # of commandline arguments


$filename = $ARGV[0];


if (@ARGV < 1 ) {


    print ("USAGE: export_buddy.pl inputfile\n");
    die ("NO input file specified...\n");
}


elsif (@ARGV > 1 )



    print ("USAGE: export_buddy.pl inputfile\n");
    die ("Only '1' input file allowed...\n");
}


else {


    open (INFILE, "$filename") || die ("Cannot open input file
'$filename'.\n");


}



#Open Output File




$outfile = join ("", $filename, ".wri");
open (outfile, "> $outfile") || die("Cannot open output file.\n");
select(outfile);


$pkg_or_func = 0;
$prior_cmt = 0;
$cmd = "";




print STDOUT ("\n\nOpening:\ninput file : '$filename' \n");
print STDOUT ("output file: '$outfile' \n");

$line = <INFILE>;
$linectr = 1;
$prior_line = length($line);





while (! eof()) {    #while.1


    #get the line length


    $prior_line = length($line);



#print("prior: $prior_line len: $line_len  lctr: $linectr line: '$line'\n");




    #now remove the 1st leading space


    $line =~ s/^ //;


    #remove all the double quotes


    $line =~ s/\"//g;


    #remove the linefeed char


    $lastchr = chomp ($line);


    if ($line =~ /^. importing/ ) { #if.import

        print ("\n--  \n");
        print ("-- *************************************\n");
        print ("-- Switch User: $line \n");
        print ("-- *************************************\n");
        print ("--  \n");
        print (" \n");
        #move to a new line.
        #call sub to read 1 line of input
        &read_line;




    }   #end:if.1


    else { #elsif.1

         #has to be ddl or code...
         #if line is start of create package or function, don't break the
line
         #test to see if it's a chunk of code (special formatting required)
         #case insensative search
         if ( ($line =~ /^create package|^create view|^create force
view|^create procedure|^create trigger/i )) { #if.codeseg1
             #join all the lines until another command and print it.
             $line .= "\n";
             $cmd = $line;
             $linectr = 1;
             $end_of_cmd = 0;
             while ($end_of_cmd eq 0) { #while.2
             #call sub to read 1 line of input
             &read_line;
                 #if it's NOT a new command, process it and print it
                 if (!($line =~ /^analyze |^create |^alter |^grant |^revoke


|^comment on |^\. impor|^import terminated/i ))


                     #compare line lengths and decide if should join or add
linefeed
                     if ($prior_line > 78) { #if.len1
                         if (($line_len < 80) && ($line !~ /^--\s/) )

                             #remove the last lfcr and append this line to
it
                             $cmd = substr($cmd,0, length($cmd) -1);
                             $prior_line = length($save_line);
                         }
                     }   #if.len1
                     else {
                         $prior_line = length($save_line);
                     }
                     $line .= "\n";
                     $cmd .= $line;
                 }   #if.not.cmd
                 else {
                     #restore original line (it will end up getting
reprocessed)
                     $line = $save_line;
                     #set flag to exit while statement
                     $end_of_cmd = 1;
                     # now print the previous command
                     #if view, try and format it
                     if ($cmd =~ /^create view |^create force view /i ) {
                         print &format_line("CREATE VIEW", $cmd,"\/ \n");
                     }
                     else { #print raw text
                         print ("$cmd\/\n");
                     }
                     $cmd = "";
                 }
             }   #while.2
         }   #if.codeseg1
         else {
             #Expect it to be regular command (create table, user, alter...)
             #case insensitive search
             if (($line =~ /^analyze |^create |^alter |^grant |^revoke
|^comment on |^import terminated/i )) { #if.ddlseg1
                 #join all the lines until another command and print it.
                 $cmd = $line;
                 $linectr = 1;
                 $end_of_cmd = 0;
                 while ($end_of_cmd eq 0) { #while.3
                     #call sub to read 1 line of input
                     &read_line;
                     #if it's NOT a new command, process it and print it
                     if (!($line =~ /^analyze |^create |^alter |^grant


|^revoke |^comment on |^\. impor/i ))


                         $cmd .= $line;
                     }   #if.not.cmd2
                     else {
                         #restore original line (it will end up getting
reprocessed)
                         $line = $save_line;
                         #set flag to exit while statement
                         $end_of_cmd = 1;
                         # now print the previous command
                         #Call routine to decide what it is and format/print
                         $cmd .= "\n";
                         &check_cmd;
                         $cmd = "";
                     }
                 }   #while.3
             }   #if.ddlseg1
             else {
                #call sub to read 1 line of input
                &read_line;
             }
         }   #else.codeseg1




    }    #elsif.1


    if ( $line =~ /^import terminated/i) { #elsif.1

        print ("\n--  \n");
        print ("-- *\n");
        print ("-- *** $line \n");
        print ("-- *\n");
        print ("--  \n");
        print (" \n");
        #move to a new line.
        #call sub to read 1 line of input
        &read_line;




    }


}   #end: while.1



# -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-


# Section: End Of Program


# -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-


#




select (STDOUT);

print ("\n\nClosing Files...\n");
close (outfile);
print ("Starting File Viewer...\n");
system( 'start', 'ddlfile.txt');
print ("Total lines read: $totlines\n");
print ("All Done...\n");





#


### -------------------------------- End Of



Main --------------------------------




#





## -------------------------------------------------------------------------


##


#  Subroutines Defined Below


## -------------------------------------------------------------------------


#  check_cmd : calls format_line after determining what cmd it is


#  format_line : formats commands


#  read_line    : reads 1 line of input


## -------------------------------------------------------------------------


##


#





sub read_line {


    $line = <INFILE>;


    #allows me to bail out if at eof


    if (eof()) {$end_of_cmd = 1;}


    $linectr += 1;


    if ($linectr > 999) {

        print STDOUT (".");
        $totlines += $linectr;
        $linectr = 0;




    }


    $save_line = $line;


    #get the line length


    $line_len = length($line);


    #remove all the double quotes (may rethink this if people use "' with
lcase name...)


    $line =~ s/\"//g;


    #now remove the 1st leading space


    $line =~ s/^ //;


    #remove the crlf


    $lastchr = chomp ($line);


}



sub check_cmd {

       if ($cmd =~ /^create table /i ) {
           print &format_line("CREATE TABLE", $cmd, "\/ \n");
       }
       elsif ($cmd =~ /^comment on /i ) {
           print ( $cmd , "\/ \n");
       }
       #ignore these lines.
       elsif ($cmd =~ /^alter session set current_schema/i ) {
           $cmd = "";
       }
       elsif ($cmd =~ /^create index |create unique index /i ) {
           print &format_line("CREATE INDEX", $cmd, "\/ \n");
       }
       elsif ($cmd =~ /^create tablespace /i ) {
           print &format_line("CREATE TABLESPACE", $cmd, "\/ \n");
       }
       elsif ($cmd =~ /^create rollback segment /i ) {
           print &format_line("CREATE ROLLBACK SEGMENT", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^alter table /i ) {
           print &format_line("ALTER TABLE", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^create user |^alter user /i ) {
           print &format_line("CREATE USER", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^alter resource /i ) {
           print &format_line("ALTER RESOURCE", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^create sequence /i ) {
           print &format_line("CREATE SEQUENCE", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^create view |^create force view /i ) {
           print &format_line("CREATE VIEW", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^create profile /i) {
           print &format_line("CREATE PROFILE", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^create database link /i) {
           print &format_line("CREATE DATABASE LINK", $cmd,"\/ \n");
       }
       elsif ($cmd =~ /^alter schema /i) {
           print ("-- \n");
           print ("-- *****************************************\n");
           print ("-- $cmd");
           print ("-- *****************************************\n");
           print ("-- \n\n");
       }
       else {
           print &format_line("DUMMY", $cmd,"\/ \n");
       }




}



sub format_line {


   my ($break_str, $temp, $crlf) = @_;


   local($result) = "";


   if ($break_str eq "CREATE TABLE") {

      $temp =~ s/STORAGE/\nSTORAGE/g;
      $temp =~ s/\), /\)\n     , /g;
      $temp =~ s/NOT NULL,/NOT NULL\n     , /g;
      $temp =~ s/DATE, /DATE\n     , /g;
      $temp =~ s/NUMBER, /NUMBER\n     , /g;
      $temp =~ s/ROWID, /ROWID\n     , /g;
      $temp =~ s/BLOB, /BLOB\n     , /g;
      $temp =~ s/CLOB, /CLOB\n     , /g;
      $temp =~ s/NCLOB, /NCLOB\n     , /g;
      #oracle 8 stuff
      $temp =~ s/ BUFFER_POOL /\n     BUFFER_POOL /g;
      $temp =~ s/ LOGGING /\nLOGGING /g;
      $temp =~ s/ NOLOGGING /\nNOLOGGING /g;
      $temp =~ s/ NOT NULL ENABLE \,/NOT NULL ENABLE\n     ,/g;
      $temp =~ s/ PARTITION BY /\nPARTITION BY /g;
      $temp =~ s/ , PARTITION /\n   , PARTITION /g;
      $temp =~ s/\) INDEX \(/\) \nINDEX \(/g;
      $temp =~ s/ CHUNK /\nCHUNK /g;
      $temp =~ s/ PCTVERSION /\nPCTVERSION /g;
      $temp =~ s/ NOCACHE /\nNOCACHE /g;
      $temp =~ s/ CACHE /\nCACHE /g;
      #storage parms
      $temp =~ s/PCTFREE / \nPCTFREE /g;
      $temp =~ s/PCTUSED / \nPCTUSED /g;
      $temp =~ s/INITRANS / \nINITRANS /g;
      $temp =~ s/MAXTRANS / \nMAXTRANS /g;
      $temp =~ s/INITIAL / \nINITIAL /g;
      $temp =~ s/NEXT / \n     NEXT /g;
      $temp =~ s/MINEXTENTS / \n     MINEXTENTS /g;
      $temp =~ s/MAXEXTENTS / \n     MAXEXTENTS /g;
      $temp =~ s/PCTINCREASE / \n     PCTINCREASE /g;
      $temp =~ s/FREELIST GROUPS / \n     FREELIST GROUPS /g;
      $temp =~ s/FREELISTS / \n     FREELISTS /g;
      $temp =~ s/\) TABLESPACE/ \)\nTABLESPACE /g;


   }


   elsif ($break_str eq "CREATE INDEX") {

      $temp =~ s/,/\n     ,/g;
      #oracle 8 stuff
      $temp =~ s/ BUFFER_POOL /\n     BUFFER_POOL /g;
      $temp =~ s/ LOGGING /\nLOGGING /g;
      $temp =~ s/ NOT NULL ENABLE ,/NOT NULL ENABLE\n     ,/g;
      $temp =~ s/ PARTITION BY /\nPARTITION BY /g;
      $temp =~ s/ , PARTITION /\n   , PARTITION /g;
      #storage parms
      $temp =~ s/PCTFREE / \nPCTFREE /g;
      $temp =~ s/INITRANS / \nINITRANS /g;
      $temp =~ s/MAXTRANS / \nMAXTRANS /g;
      $temp =~ s/STORAGE \(/\nSTORAGE \(/g;
      $temp =~ s/INITIAL / \nINITIAL /g;
      $temp =~ s/NEXT / \n     NEXT /g;
      $temp =~ s/MINEXTENTS / \n     MINEXTENTS /g;
      $temp =~ s/MAXEXTENTS / \n     MAXEXTENTS /g;
      $temp =~ s/PCTINCREASE / \n     PCTINCREASE /g;
      $temp =~ s/FREELISTS / \n     FREELISTS /g;
      $temp =~ s/\) TABLESPACE/ \)\nTABLESPACE /g;


   }


   elsif ($break_str eq "CREATE TABLESPACE")


      $temp =~ s/DATAFILE / \nDATAFILE /g;
      $temp =~ s/, '/ \n        , '/g;
      $temp =~ s/DEFAULT STORAGE /\nDEFAULT STORAGE /g;
      $temp =~ s/INITIAL / \n        INITIAL /g;
      $temp =~ s/NEXT / \n        NEXT /g;
      $temp =~ s/MINEXTENTS / \n        MINEXTENTS /g;
      $temp =~ s/MAXEXTENTS / \n        MAXEXTENTS /g;
      $temp =~ s/PCTINCREASE / \n        PCTINCREASE /g;


   }


   elsif ($break_str eq "CREATE ROLLBACK SEGMENT")


      $temp =~ s/STORAGE/\nSTORAGE/g;
      $temp =~ s/TABLESPACE/ \nTABLESPACE/g;
      $temp =~ s/INITIAL / \n     INITIAL /g;
      $temp =~ s/NEXT / \n     NEXT /g;
      $temp =~ s/MINEXTENTS / \n     MINEXTENTS /g;
      $temp =~ s/MAXEXTENTS / \n     MAXEXTENTS /g;


   }


   elsif ($break_str eq "ALTER TABLE")


      $temp =~ s/ADD CONSTRAINT / \nADD CONSTRAINT /g;
      $temp =~ s/PRIMARY KEY / \nPRIMARY KEY /g;
      $temp =~ s/USING INDEX/ \nUSING INDEX/g;
      $temp =~ s/,/ \n      , /g;
      $temp =~ s/PCTFREE / \nPCTFREE /g;
      $temp =~ s/PCTUSED / \nPCTUSED /g;
      $temp =~ s/INITRANS / \nINITRANS /g;
      $temp =~ s/MAXTRANS / \nMAXTRANS /g;
      $temp =~ s/STORAGE \(/\nSTORAGE \(/g;
      $temp =~ s/INITIAL / \n    INITIAL /g;
      $temp =~ s/NEXT / \n    NEXT /g;
      $temp =~ s/MINEXTENTS / \n    MINEXTENTS /g;
      $temp =~ s/MAXEXTENTS / \n    MAXEXTENTS /g;
      $temp =~ s/PCTINCREASE / \n    PCTINCREASE /g;
      $temp =~ s/FREELISTS / \n    FREELISTS /g;
      $temp =~ s/TABLESPACE / \nTABLESPACE /g;
      #FOREIGN KEY stuff
      $temp =~ s/FOREIGN KEY/ \nFOREIGN KEY/g;
      $temp =~ s/REFERENCES / \nREFERENCES /g;


   }


   #create/alter user section


   elsif ($break_str eq "CREATE USER")


      $temp =~ s/IDENTIFIED BY/ \n   IDENTIFIED BY/g;
      $temp =~ s/DEFAULT TABLESPACE/ \n   DEFAULT TABLESPACE/g;
      $temp =~ s/TEMPORARY TABLESPACE/ \n   TEMPORARY TABLESPACE/g;
      $temp =~ s/QUOTA / \n      QUOTA /g;
      $temp =~ s/, / \n    , /g;




   }


   elsif ($break_str eq "CREATE DATABASE LINK")


      $temp =~ s/ CONNECT TO /\n    CONNECT TO /g;
      $temp =~ s/IDENTIFIED BY /\n    IDENTIFIED BY /g;
      $temp =~ s/ USING /\n    USING /g;


   }


   elsif ($break_str eq "ALTER RESOURCE")


      $temp =~ s/CPU_PER_SESSION/ \n CPU_PER_SESSION/g;
      $temp =~ s/LOGICAL_READS_PER_SESSION/ \n LOGICAL_READS_PER_SESSION/g;
      $temp =~ s/CONNECT_TIME/ \n CONNECT_TIME/g;
      $temp =~ s/PRIVATE_SGA/ \n PRIVATE_SGA/g;


   }


   elsif ($break_str eq "CREATE SEQUENCE")


      $temp =~ s/MINVALUE / \n   MINVALUE /g;
      $temp =~ s/MAXVALUE / \n   MAXVALUE /g;
      $temp =~ s/INCREMENT BY / \n   INCREMENT BY /g;
      $temp =~ s/START WITH / \n   START WITH /g;
      $temp =~ s/CACHE / \n   CACHE /g;
      $temp =~ s/NOORDER / \n   NOORDER /g;
      $temp =~ s/ NOCYCLE/ \n   NOCYCLE /g;


   }


   elsif ($break_str eq "CREATE VIEW")


      $temp =~ s/ AS / AS \n/ig;
      $temp =~ s/,/\n     , /g;
      $temp =~ s/group by/\n group by /ig;
      #format views
      $temp =~ s/ from /\n  FROM /ig;
      $temp =~ s/ where /\n WHERE /ig;
      $temp =~ s/ and /\n   AND /ig;




   }


   elsif ($break_str eq "CREATE PROFILE")


      $temp =~ s/COMPOSITE_LIMIT/\n   COMPOSITE_LIMIT /g;
      $temp =~ s/SESSIONS_PER_USER/\n   SESSIONS_PER_USER /g;
      $temp =~ s/CPU_PER_SESSION/\n   CPU_PER_SESSION /g;
      $temp =~ s/CPU_PER_CALL/\n   CPU_PER_CALL /g;
      $temp =~ s/LOGICAL_READS_PER_SESSION/\n   LOGICAL_READS_PER_SESSION
/g;
      $temp =~ s/LOGICAL_READS_PER_CALL/\n   LOGICAL_READS_PER_CALL /g;
      $temp =~ s/IDLE_TIME/\n   IDLE_TIME /g;
      $temp =~ s/CONNECT_TIME/\n   CONNECT_TIME /g;
      $temp =~ s/PRIVATE_SGA/\n   PRIVATE_SGA /g;


   }


   $result = join("", $temp, $crlf);


}


$retvalue = $result;








Received on Sun Oct 17 1999 - 20:23:13 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US