19

I am new to Oracle Databases and would like to develop a data dictionary and ER Diagrams for our existing databases. Do you have any tips, scripts, tools for doing so?

1

6 Answers 6

21

Oracle's Data Modeller can reverse-engineer from an existing database. They were charging for it at one time, but it is now free to use.

1
  • 4
    And it comes bundled with Oracle's SQL Developer 3 Jan 29, 2011 at 8:36
9

We're using Sybase's PowerDesigner though I admit, its quite overkill for what you want. We're using to support our full software engineering effort, from diagraming use-cases for requirements gathering, capturing those requirements, conceptual and physical models (database, object oriented code, infrastructure, etc), requirements/functionality verification matrices...

Basically anything UML-oriented that helps us to discover, architect, track and implement business solutions from an IT perspective.

Don't be fooled by the "Sybase" name - it supports MANY major RDBMS's and UML diagram formats and has templates for them all. Its a very cool tool.

6

The free oracle sql developer data modeller works well. note that this is a seperate download than the one bundled with sql developer. The data model tool in sql developer does not have as many features.

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

It has some video walkthroughs.

file-> import -> data dictionary. You add a connection to a database and decide what you want to reverse engineer.

Note that the model part will be a mess and will take you time to make it neat enough to read. When oracle reverse engineers, it gives you a physical model and not an ERD. You can change the Physical Model into an ERD. However, most people do not know what an ERD is and think an ERD is a physical model. ERDs have minimal value at the stage where your data model exists. There is no good reason to keep a physical model (which is easier for developers to read) and an ERD.

As far as data dictionary... This usually means documentation about your data model. The best way to do that is to use the tool and to use the "comment" command. Oracle allows you to create comments for all tables, columns and objects and store them in the database. This way you can have documentation in the database that is queriable. Sql Developer data modeller can query this.

You can do this in the GUI, but I find it faster to use "comment" commands and put it in the database from sql scripts, then pull it, instead of clicking on each object. Google "oracle sql comment" for details on how this works.

5

I know that SQLServer works with Visio; I'm not sure if Oracle does or not, never tried it, but Visio has a LOT of tricks up its sleeves.

Also, if you're in a reasonably large business, Visio isn't that hard to scrape up a copy of, albeit the license can be a little rich. But professional teams in reasonably large businesses usually already have Visio so it's not that big of a deal.

If anyone can deny or confirm that Visio will do this for Oracle as it does for SqlServer that would be awesome.

2
  • Visio comes with an Oracle driver - you can reverse engineer Oracle databases with it. Note that you need Visio Pro for the database reverse engineer feature. Another nice feature of visio is that you can add back FK relationships that are missing from the database. This makes the structure much clearer. You can also make multiple diagrams from the same repository to illustrate different parts of the model. Nov 15, 2011 at 21:01
  • Today I learned...
    – jcolebrand
    Nov 15, 2011 at 21:20
2

I answered same question in https://stackoverflow.com/questions/8140387/oracle-extract-graphical-database-representation/8149947#8149947 See below.

For a free tool I recommend schema spy, http://schemaspy.sourceforge.net/. It needs Java Runtime. Look to http://schemaspy.sourceforge.net/sample/relationships.html to see diagrams it generate.

simple usage with oracle hr template is given below.

SET JDBC_ORACLE_JAR=C:\oracle\product\10.2.0\client_1\jdbc\lib\ojdbc14.jar
SET CONNECTION_USERNAME=hr
SET CONNECTION_PASSWORD=hr
SET TNS_DATABASE_NAME=orcl
SET SCHEMA_NAME=HR
SET TABLE_NAME='EMP'
SET OUTPUT_NAME=HR_EMP

java -jar schemaSpy_5.0.0.jar -dp %JDBC_ORACLE_JAR% -db %TNS_DATABASE_NAME% -o     schemaOutput%OUTPUT_NAME% -u %CONNECTION_USERNAME% -p %CONNECTION_PASSWORD% -i %TABLE_NAME%.* -schemas %SCHEMA_NAME% -hq -noviews -loglevel severe

java -jar schemaSpy_5.0.0.jar -dp %JDBC_ORACLE_JAR% -db %TNS_DATABASE_NAME% -o     schemaOutput%OUTPUT_NAME% -u %CONNECTION_USERNAME% -p %CONNECTION_PASSWORD%  -schemas %SCHEMA_NAME% -hq -noviews -loglevel severe

First one will give you diagram with tables starting with EMP . Second one will give you diagram with all tables in hr schema .

1
  • I would like to ask a question. What is etiquette of dealing with duplicate questions and answers? Nov 16, 2011 at 10:21
1

We use Dataedo for generating data dictionaries and ERDs for all our databases - Oralce & SQL Servers. You just connect to a database, schema is imported into repository and you can describe each element with UI. Tool enables splitting large databases into logical modules, defining missing FKs and UKs, use rich text and paste images. Using physical and logical FKs tool creates simple ERDs.

Each time database schema changes you can import those changes and they are automatically reflected in repository.

Data dictionary with ERDs are exported to PDF or interactive HTML.

I'm the product manager of Dataedo.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.