Re: Oracle Database Schema Diagram Generator

From: Tim X <timx_at_nospam.dev.null>
Date: Mon, 21 Sep 2009 18:12:23 +1000
Message-ID: <8763bc90ns.fsf_at_lion.rapttech.com.au>



yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) writes:

> Shakespeare (whatsin_at_xs4all.nl) wrote:
> : Sashi schreef:
> : > On Sep 18, 1:58 pm, Spiff McClure <sardonicstuffedti..._at_gmail.com>
> : > wrote:
> : >> I've been searching around and I'd like to know if anyone can
> : >> recommend a decent tool to generate a Database Schema Diagram from an
> : >> Oracle DB. I have an existing database that is very large and complex
> : >> so generating a visual to explain it's current tables and their
> : >> relationships would help a lot of people out.
> : >>
> : >> Free and open source tools preferred but I might be willing to use
> : >> something else if the tools is good and highly recommended.
> : >>
> : >> Thanks
> : >
> : > I use Aqua Data Studio as a sql client and it has a useful diagram
> : > generator. They do have a 30 day trial so you can try it out. I've
> : > used this to generate diagrams and works ok. I'm not a picky and heavy
> : > weight user so YMMV.
> : > HTH,
> : > Sashi
>
> : Oracle SQL Developer Data Modeler?
>
> Graphviz to do the graphing.
>
> The details for the graphing statements can be extracted from tables such
> as USER_CONS_COLUMNS. The graphviz dot language is extremely simple, you
> can get a basic graph with statements such as
> DEPARTMENTS -> EMPLOYEES ;
> DEPARTMENTS -> LOCATIONS ;
> EMPLOYEES -> DEPARTMENTS ;
>
> The "record" format is more involved, but can make nodes that look pretty
> much like a regular ERD.

This is what I tend to use as well. In addition, there is a little (perl?) script out there called sqlFairy which will generate the graphviz 'dot' files from the database or from an sql script that defines the tables (such as the ones some tools will export). I've found it works pretty well, but some forms that are valid to Oracle are not picked up by the script. For example

CREATE TABLE taba (

    id NOT NULL CONSTRAINT taba_id_fk

                REFERENCES parent_a(id)

    ....
);

won't work, but

CREATE TABLE taba (

       id NUMBER(7( NOT NULL CONSTRAINT taba_id_fk
                             REFERENCES parent_a(id)
       ....

);

will work, yet both are legal (though possibly questionable) syntax.

The sqlfairy script uses perl DBI to query the database directly, so you need DBD:Oracle to get it to work if you don't have sql scripts that define the database structure.

The extent to which you get good rsults with this tool depends a lot on how good the database design is. If column names are used in a consistent manner and you have good constraint definitions, its pretty good. Initial output from graphviz can need a bit of tweaking and you may need to try the different graphing algorithms it uses to get a really good diagrom.

My experience however is that nearly all graphing solutions tend to break down pretty quickly once you have a moderate to complex schema to work with. However, I find this to be one of the real strengths of graphviz - you can easily edit the dot files and even break up the diagrom into smaller 'chunks' that don't require a printer capable of printing on pages that are larger than some countries!

Tim

-- 
tcross (at) rapttech dot com dot au
Received on Mon Sep 21 2009 - 03:12:23 CDT

Original text of this message