On my current project, our team inherited a system that had already been in production for years.

We were immediately tasked with making some modifications. One problem: we didn't have a development system. Setting up one would be easy enough -- except that we found that the production Oracle database had been modified over the years, so that the build scripts that we inherited no longer could create an accurate clone of the database. Adding to the complication was the fact that the production database was on a classified system, so I had to avoid retrieving the data (I got an OK on the DB structure).

What we needed to do was regenerate the build scripts from the existing database -- essentially reverse engineering the Oracle database.

I did some research, and found that you can extract the database structure information from the database itself (this article is good), but manually reconstructing the build scripts would be pretty labor-intensive. Another solution had a program that would get the table definitions from an Oracle dump file, but I also needed the stored procedure information, too.

Finally, while reading about Oracle's import and export utilities, I found that imp has a parameter (show=y) that will write out the dump file from Oracle's proprietary format to ASCII.

So I created a dump file with none of the row information:

$ exp file=myApp_exp owner=myUser rows=n grants=n

Then I ran imp on it to get an ASCII dump:

$ imp file=myApp_exp show=y grants=n indexes=n rows=n compile=n full=y log=myApp-ddl.txt

…and I'm done! Well, no. The output file is in ASCII, but for some reason it's in this horrible format (table/column names changed):

    "CREATE TABLE "XYZ" ("XYZ_CD" CHAR(32) NOT NULL ENABLE, "CLASS_CD" CHAR(32) "
    "NOT NULL ENABLE, "XYZ_ACTIVE_CD" CHAR(1) NOT NULL ENABLE, "XYZ_VER_TX" VARC"
    "HAR2(25) NOT NULL ENABLE, "XYZ_DSC_TX" VARCHAR2(2500), "XYZ_ABCDEFG_BGN_CD""
    " CHAR(1) NOT NULL ENABLE, "XYZ_RG_STAT_CD" CHAR(32) NOT NULL ENABLE, "XYZ_T"
    "P_CD" CHAR(32) NOT NULL ENABLE, "ORG_MGMT_OFFICE_CD" CHAR(32) NOT NULL ENAB"
    "LE, "XYZ_SHARE_SYS_CD" CHAR(1) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INIT"
    "RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TA"
    "BLESPACE "USERS" LOGGING NOCOMPRESS"
    "CREATE UNIQUE INDEX "XYZ_PK" ON "XYZ" ("XYZ_CD" ) PCTFREE 10 INITRANS 2 MA"
    "XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "
    ""USERS" LOGGING"
    "ALTER TABLE "XYZ" ADD CONSTRAINT "XYZ_CD_PK" PRIMARY KEY ("XYZ_CD") USING "
    "INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 "
    "FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING ENABLE "
    "COMMENT ON TABLE "XYZ" IS 'This is a comment."

It's still a long way from being a script that I can use to generate a new database. At least it's consistent -- quotes around every line, lines truncated if the line is too long, no semicolons denoting end of command... all it needs is some text processing. This sounds like a job for Perl!

So I wrote the attached Perl script to parse the file into a script that SQL*Plus could actually run.

$ ./exp2ddl-onefile.pl myApp-ddl.txt >myApp-ddl.sql

I did have to remove the top couple lines of the .sql script (what is sys.dbms_logrep_imp.instantiate_schema anyway?), but once I did that it ran fine, and all the tables, comments, stored procedures, triggers, foreign keys, and indexes were all created perfectly.

The problem with that file was that the resulting script was almost a megabyte in size! Also, it would have been helpful for when I need to look at individual stored procedures if they were broken out into separate files. So, I rewrote the Perl script to break up every command and put it into a file. While I was at it, I generated the files so that they were organized into a directory structure, so Functions, Procedures, Triggers, Tables, etc. were all put into directories.

$ ./exp2ddl.pl myApp-ddl.txt

...and voila! I've got the entire database (sans data) broken down into a bunch of organized files, with one main sql script used to call each of them.

Does anyone have a better way?